create table #hd(A INT,B INT)
INSERT #hd select 1,4
union all select 2,5
union all select 3,6
select *from #hd
目前查询结果
A B
1 4
2 5
3 6
最终想实现结果
A 1 2 3
B 4 5 6
------解决方案--------------------
create table #hd(A INT,B INT)
INSERT #hd select 1,4
union all select 2,5
union all select 3,6
select *from #hd
SELECT '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)
*/