How to create a categorial column from two numeric columns in SQL Server

zpjtge22 于 2023-04-04 发布在 Go

关注(0)|答案(1)|浏览(134)

I would like to ask you how to create a class column from tow numeric columns in SQL Server like below.

Thank you for your help

DECLARE @Table TABLE (ID INT, Name NVARCHAR(20), Value1 INT, Value2 INT);
INSERT INTO @Table (ID, Name, Value1, Value2) VALUES
(1, 'A', 5, 2), (2, 'B', 6, 3), (3, 'C', 9, 4), (4, 'D', 8, 7),  
(5, 'E', 2, 10);

sql-server

来源：https://stackoverflow.com/questions/75879670/how-to-create-a-categorial-column-from-two-numeric-columns-in-sql-server

1条答案

按热度按时间

irlmq6kh1#

The way you do this is going to depend on how exactly you want your result to work. If you just want get the results of value1 and value2 on a per row basis you could:

SELECT *, '['+CAST(value1 AS VARCHAR)+'-'+CAST(value2 AS VARCHAR)+']'
  FROM @Table;

ID	Name	Value1	Value2	(No column name)
1	A	5	2	[5-2]
2	B	6	3	[6-3]
3	C	9	4	[9-4]
4	D	8	7	[8-7]
5	E	2	10	[2-10]

If you want to consider the full path (who's this ones parent?), you'll need some sort of recursive behavior, like:

;WITH rCTE AS (
SELECT ID, Name, Value1, Value2, CAST(CAST(value1 AS VARCHAR)+'-'+CAST(value2 AS VARCHAR) AS VARCHAR(MAX)) AS Target
  FROM @table
 WHERE Value1 NOT IN (SELECT value2 FROM @Table)
UNION ALL
SELECT r.ID, r.Name, r.Value1, r.Value2, a.Target + '-' + CAST(r.Value2 AS VARCHAR)
  FROM rCTE a
    INNER JOIN @Table r
      ON a.Value2 = r.Value1
)

SELECT ID, Name, Value1, Value2, '['+Target+']'
  FROM rCTE

ID	Name	Value1	Value2	(No column name)
1	A	5	2	[5-2]
2	B	6	3	[6-3]
3	C	9	4	[9-4]
4	D	8	7	[8-7]
5	E	2	10	[5-2-10]

赞(0）回复(0）举报 2023-04-04

我来回答

How to create a categorial column from two numeric columns in SQL Server

1条答案

相关问题

热门标签

最新问答