Is this possible using UNPIVOT ? or any other logic...?

Question

My source table is as below:

declare @table table (EvalId int, ProjectId int,RT1 int,RT2 int,RT3 int)

insert into @table
select 21 evalID,17 ProjectID, 0 RT1,8 Rt2, 7 RT3
union all
select 21 evalID,18 ProjectID, 4 RT1,6 Rt2, 1 RT3

select * from @table

The required output is:

EvalId  ProjectId   RT  Rating
21      17          RT1 0
21      17          RT2 8
21      17          RT3 7
21      18          RT1 4
21      18          RT2 6
21      18          RT3 1

How can I do this using UNPIVOT?

Answer 1

;WITH CTE
as
 (
  select * from @table
    UNPIVOT
    (
         Rating FOR RT IN (RT1,RT2,RT3)
    ) as pv
)
SELECT EvalId   
       ,ProjectId   
       ,RT
       ,Rating
FROM CTE

Result Set

╔════════╦═══════════╦═════╦════════╗
║ EvalId ║ ProjectId ║ RT  ║ Rating ║
╠════════╬═══════════╬═════╬════════╣
║     21 ║        17 ║ RT1 ║      0 ║
║     21 ║        17 ║ RT2 ║      8 ║
║     21 ║        17 ║ RT3 ║      7 ║
║     21 ║        18 ║ RT1 ║      4 ║
║     21 ║        18 ║ RT2 ║      6 ║
║     21 ║        18 ║ RT3 ║      1 ║
╚════════╩═══════════╩═════╩════════╝