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

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);

>Solution :

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;

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