Sql Server 2008 行列互转问题

思路：

1、在代码中拆分、组装数据, 如：高级语言、SQL存储过程

2、使用 SQL 中 Case、Group by 分组

3、自定义 SQL 函数

4、使用 SQL 中游标 或 XML Path

5、PIVOT 和 UNPIVOT

实例：

1、行转列： PIVOT

示例：

源表：

PIVOT 结果表：

测试 SQL 语句：

CREATE TABLE dbo.pvt ( month int, val int);
GO
INSERT INTO dbo.pvt VALUES
(1,10)
,(2,20)
,(3,30)
,(4,40)
,(5,50)
,(6,60)
,(7,70)
,(8,80)
,(9,90)
,(10,100)
,(11,110)
,(12,120)
GO

declare @sql varchar(100)
select @sql = isnull(@sql + '],[' , '') + Cast(month as varchar) from pvt group by month
set @sql = '[' + @sql + ']'
exec ('select * from (select * from pvt) a pivot (max(val) for month in (' + @sql + ')) b')

2、列转行： UNPIVOT

示例：

源表：

UNPIVOT 结果表：

测试 SQL 语句：

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

参考：

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table.

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

Reference:

http://technet.microsoft.com/zh-cn/library/ms177634.aspx