如何在SQL中使用PIVOT [英] How to use PIVOT in SQL

问题描述

我有这样的结果集

ItemCode值1值2值3值4

项目1 1 2 3 4
Item2 5 6 7 8
Item3 9 10 11 12


我需要将此表转换为以下格式

Item1 Item2 Item3
1 5 9
2 6 10
3 7 11
4 8 12

I am Having a Result Set Like This

ItemCode Value1 Value2 Value3 Value4

Item1 1 2 3 4
Item2 5 6 7 8
Item3 9 10 11 12


I need to convert this table to following format

Item1 Item2 Item3
1 5 9
2 6 10
3 7 11
4 8 12


How to do this??

推荐答案

select * from (select ItemCode,Value1  from tbl_test) AS SourceTable
PIVOT
(
    max(Value1)
FOR ItemCode IN ([Item1],[Item2],[Item3])
) AS PivotTable
Union
select * from (select ItemCode,Value2  from tbl_test) AS SourceTable
PIVOT
(
    max(Value2)
FOR ItemCode IN ([Item1],[Item2],[Item3])
) AS PivotTable
Union
select * from (select ItemCode,Value3  from tbl_test) AS SourceTable
PIVOT
(
    max(Value3)
FOR ItemCode IN ([Item1],[Item2],[Item3])
) AS PivotTable
Union
select * from (select ItemCode,Value4  from tbl_test) AS SourceTable
PIVOT
(
    max(Value4)
FOR ItemCode IN ([Item1],[Item2],[Item3])
) AS PivotTable;

这篇关于如何在SQL中使用PIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！