SQL SERVER 2005 行转列
SQL SERVER 2005 以上版本为我们提供了PIVOT来实现行到列的转换，这里建立一个测试数据库对其进行简单的测试：
显示代码
1 USE[master]
2 GO
3 /****** Object: Database [Demo] Script Date: 07/27/2011 16:03:21 ******/
4 CREATEDATABASE[Demo]
5 GO
6 USE[Demo]
7 GO
8 /****** Object: Table [dbo].[Customer] Script Date: 07/27/2011 16:03:21 ******/
9 CREATETABLE[dbo].[Customer](
10 [Id][uniqueidentifier]NOTNULL,
11 [Name][nvarchar](49) NOTNULL,
12 CONSTRAINT[PK_Customer]PRIMARYKEYNONCLUSTERED
13 (
14 [Id]ASC
15 )
16 )
17 GO
18 CREATENONCLUSTEREDINDEX[IX_Customer_Name]ON[dbo].[Customer]
19 (
20 [Name]ASC
21 )
22 GO
23 /****** Object: Table [dbo].[Car] Script Date: 07/27/2011 16:03:21 ******/
24 CREATETABLE[dbo].[Car](
25 [Id][uniqueidentifier]NOTNULL,
26 [Name][nvarchar](49) NOTNULL,
27 CONSTRAINT[PK_Car]PRIMARYKEYNONCLUSTERED
28 (
29 [Id]ASC
30 )
31 )
32 GO
33 CREATENONCLUSTEREDINDEX[IX_Car_Name]ON[dbo].[Car]
34 (
35 [Name]ASC
36 )
37 GO
38 /****** Object: Table [dbo].[Order] Script Date: 07/27/2011 16:03:21 ******/
39 CREATETABLE[dbo].[Order](
40 [Id][uniqueidentifier]NOTNULL,
41 [CarId][uniqueidentifier]NOTNULL,
42 [CustomerId][uniqueidentifier]NOTNULL,
43 [Number][int]NOTNULL,
44
45 CONSTRAINT[PK_Order]PRIMARYKEYNONCLUSTERED
46 (
47 [Id]ASC
48 )
49 )
50 GO
51 /****** Object: Default [DF_Customer_Id] Script Date: 07/27/2011 16:03:21 ******/
52 ALTERTABLE[dbo].[Customer]ADDCONSTRAINT[DF_Customer_Id]DEFAULT (newid()) FOR[Id]
53 GO
54 /****** Object: Default [DF_Car_Id] Script Date: 07/27/2011 16:03:21 ******/
55 ALTERTABLE[dbo].[Car]ADDCONSTRAINT[DF_Car_Id]DEFAULT (newid()) FOR[Id]
56 GO
57 /****** Object: Default [DF_Order_Id] Script Date: 07/27/2011 16:03:21 ******/
58 ALTERTABLE[dbo].[Order]ADDCONSTRAINT[DF_Order_Id]DEFAULT (newid()) FOR[Id]
59 GO
60 /****** Object: ForeignKey [FK_Order_Car] Script Date: 07/27/2011 16:03:21 ******/
61 ALTERTABLE[dbo].[Order]WITHCHECKADDCONSTRAINT[FK_Order_Car]FOREIGNKEY([CarId])
62 REFERENCES[dbo].[Car] ([Id])
63 GO
64 ALTERTABLE[dbo].[Order]CHECKCONSTRAINT[FK_Order_Car]
65 GO
66 /****** Object: ForeignKey [FK_Order_Customer] Script Date: 07/27/2011 16:03:21 ******/
67 ALTERTABLE[dbo].[Order]WITHCHECKADDCONSTRAINT[FK_Order_Customer]FOREIGNKEY([CustomerId])
68 REFERENCES[dbo].[Customer] ([Id])
69 GO
70 ALTERTABLE[dbo].[Order]CHECKCONSTRAINT[FK_Order_Customer]
71 GO
随意插入一些测试数据后写了如下语句：
显示代码
1 select*from (
2 select
3 c.Name as 名称,
4 o.NumberasNumber,
5 s.Name as Customer
6 from[Order] o
7 innerjoin Car c on o.CarId=c.Id
8 innerjoin Customer s on o.CustomerId=s.Id
9 ) t
10 PIVOT ( sum(Number) FOR Customer IN(张三,李四)) as p
还有一点问题没有解决，由于PIVOT中的IN不支持子查询，所以给直接写死到代码里了，这里采用了一个不太好的解决方法:拼接SQL：
显示代码
1 declare@sqlStrnvarchar(max)
2 set@sqlStr='
3 select * from (
4 select
5 c.Name as 名称,
6 o.Number as Number,
7 s.Name as Customer
8 from [Order] o
9 inner join Car c on o.CarId=c.Id
10 inner join Customer s on o.CustomerId=s.Id
11 ) t
12 PIVOT ( sum(Number) FOR Customer IN('
13
14 select@sqlStr=@sqlStr+Name+','from Customer c
15 set@sqlStr=SUBSTRING(@sqlStr,0,len(@sqlStr))
16 set@sqlStr=@sqlStr+')) as p'
17
18 exec(@sqlStr)
sql server 2005的PIVOT大大简化了行转列的实现，如果再支持子查询那就更好了。
