MSSQL旋转和反旋转的例子

1.旋转

;WITH CTE AS (
    SELECT 'A' AS TAG,'NUM_1' AS ITEM,10 AS VAL
    UNION ALL
    SELECT 'A','NUM_2',9
    UNION ALL
    SELECT 'A','NUM_3',8
    UNION ALL
    SELECT 'A','NUM_4',7
    UNION ALL
    SELECT 'B','NUM_1',22
    UNION ALL
    SELECT 'B','NUM_2',23
    UNION ALL
    SELECT 'B','NUM_4',24
    UNION ALL
    SELECT 'B','NUM_5',0
)
SELECT TAG,NUM_1 AS NUM_1,NUM_2 AS NUM_2,NUM_3 AS NUM_3,NUM_4 AS NUM_4,NUM_5 AS NUM_5
FROM CTE
PIVOT(
MAX(VAL) FOR ITEM IN (NUM_1,NUM_2,NUM_3,NUM_4,NUM_5)
) AS P 
ORDER BY TAG DESC

 

2.反旋转

with cte as (
select 'A' as tag
    ,10 as num_1
    ,9 as num_2
    ,8 as num_3
    ,7 as num_4
    ,null as num_5
union 
select 'B' as tag
    ,22 as num_1
    ,23 as num_2
    ,null as num_3
    ,24 as num_4
    ,0 as num_5
)    
select tag,item,val
from (
select * from cte
) as pivotinput
unpivot
(
 val for item in (num_1,num_2,num_3,num_4,num_5)
) as pivotoutput