SQL行列转换
[if !supportLists]1 [endif]静态行列转换
[if !supportLists]1.1 [endif]Pivot
[if !supportLists]1.1.1 [endif]Pivot 语法:
PIVOT用于将列值旋转为列名（即行转列），在SQL Server 2000可以用聚合函数配合CASE语句实现
PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P
完整语法：
table_source
PIVOT(
聚合函数（value_column）
FORpivot_column
IN())
[if !supportLists]1.1.2 [endif] 示例
[if !supportLists]1.1.3 [endif]创建表
IF OBJECT_ID(N'dbo.xs') IS NOT NULL
DROP TABLE dbo.xs
GO
CREATE TABLEdbo.xs
(
name VARCHAR (20) NULL
, brand VARCHAR (20) NULL
, sales DECIMAL (20, 4) NULL
)
INSERT INTO xs
VALUES ('张三','a', 80)
INSERT INTO xs
VALUES ('张三','a', 82)
INSERT INTO xs
VALUES ('张三','b', 83)
INSERT INTO xs
VALUES ('李四','a', 80)
INSERT INTO xs
VALUES ('李四','b', 93)
INSERT INTO xs
VALUES ('李四','a', 82)
INSERT INTO xs
VALUES ('陈五','a', 80)
INSERT INTO xs
VALUES ('陈五','b', 95)
INSERT INTO xs
VALUES ('陈五','a', 80)
方法一：利用case when 或者IF 条件一个个列出来
方法二: 利用pivot
SELECT* FROM xs pivot(sum(sales) FOR brand IN (a,b)) a
[if !supportLists]1.2 [endif]Unpivot
[if !supportLists]1.2.1 [endif]Unpivot 语法
UNPIVOT用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现
完整语法：
table_source
UNPIVOT(
value_column
FORpivot_column
IN())
[if !supportLists]1. [endif]创建表
IFOBJECT_ID (N'dbo.xs2') IS NOT NULL
DROP TABLE dbo.xs2
GO
CREATETABLE dbo.xs2
(
name VARCHAR (20) NULL
, a DECIMAL (20, 4) NULL
, b DECIMAL (20, 4) NULL
)
GO
Insert intoxs2 values('d',20,49)
Insert intoxs2 values('a',20,49)
Insert intoxs2 values('b',20,49)
Insert intoxs2 values('a',20,49)
Insert intoxs2 values('b',20,49)
Insert intoxs2 values('a',20,49)
Insert intoxs2 values('b',20,49)
Insert intoxs2 values('d',20,49)
SELECTname, pp, sum(sales) FROM xs2 unpivot( sales FOR pp IN (a,b)) AS U
