create table #hd(A INT,B INT) INSERT #hd select 1,4 union all select 2,5 union all select 3,6 select *from #hdSELECT 'A' ,MAX(CASE WHEN NUM=1 THEN A ELSE NULL END) ,MAX(CASE WHEN NUM=2 THEN A ELSE NULL END) ,MAX(CASE WHEN NUM=3 THEN A ELSE NULL END) FROM ( SELECT A,B,ROW_NUMBER() OVER(ORDER BY GETDATE()) AS NUM FROM #hd )T UNION ALL SELECT 'B' ,MAX(CASE WHEN NUM=1 THEN B ELSE NULL END) ,MAX(CASE WHEN NUM=2 THEN B ELSE NULL END) ,MAX(CASE WHEN NUM=3 THEN B ELSE NULL END) FROM ( SELECT A,B,ROW_NUMBER() OVER(ORDER BY GETDATE()) AS NUM FROM #hd )T DROP TABLE #hd
select c,[1] 'v1',[2] 'v2',[3] 'v3' from (select c,v,row_number() over(partition by c order by v) 'rn' from #hd t unpivot(v for c in([A],[B])) u) a pivot(max(v) for rn in([1],[2],[3])) b/* c v1 v2 v3 ---------- ----------- ----------- ----------- A 1 2 3 B 4 5 6(2 row(s) affected) */
INSERT #hd select 1,4
union all select 2,5
union all select 3,6
select *from #hdSELECT 'A'
,MAX(CASE WHEN NUM=1 THEN A ELSE NULL END)
,MAX(CASE WHEN NUM=2 THEN A ELSE NULL END)
,MAX(CASE WHEN NUM=3 THEN A ELSE NULL END)
FROM (
SELECT A,B,ROW_NUMBER() OVER(ORDER BY GETDATE()) AS NUM
FROM #hd
)T
UNION ALL
SELECT 'B'
,MAX(CASE WHEN NUM=1 THEN B ELSE NULL END)
,MAX(CASE WHEN NUM=2 THEN B ELSE NULL END)
,MAX(CASE WHEN NUM=3 THEN B ELSE NULL END)
FROM (
SELECT A,B,ROW_NUMBER() OVER(ORDER BY GETDATE()) AS NUM
FROM #hd
)T
DROP TABLE #hd
select c,[1] 'v1',[2] 'v2',[3] 'v3'
from
(select c,v,row_number() over(partition by c order by v) 'rn'
from #hd t
unpivot(v for c in([A],[B])) u) a
pivot(max(v) for rn in([1],[2],[3])) b/*
c v1 v2 v3
---------- ----------- ----------- -----------
A 1 2 3
B 4 5 6(2 row(s) affected)
*/