表脚本和样本数据
CREATE TABLE [TableName](
[year] [nvarchar](50) NULL,
[month] [int] NULL,
[total] [int] NULL,
[loop] [nvarchar](50) NULL
)
INSERT [TableName] ([year], [month], [total], [loop]) VALUES (N'2012', 1, 20, N'loop1')
INSERT [TableName] ([year], [month], [total], [loop]) VALUES (N'2012', 2, 30, N'loop1')
INSERT [TableName] ([year], [month], [total], [loop]) VALUES (N'2012', 1, 10, N'loop2')
INSERT [TableName] ([year], [month], [total], [loop]) VALUES (N'2012', 2, 5, N'loop2')
INSERT [TableName] ([year], [month], [total], [loop]) VALUES (N'2012', 1, 50, N'loop3')
INSERT [TableName] ([year], [month], [total], [loop]) VALUES (N'2012', 2, 60, N'loop3')
使用枢轴功能…
SELECT *
FROM TableName
PIVOT(Max([total])
FOR [loop] IN ([loop1], [loop2], [loop3]) ) pvt
在线演示：http ://www.sqlfiddle.com/#!18/ 164a4/1/0
如果您正在寻找动态解决方案，请尝试此…（Dynamic Pivot）
DECLARE @cols AS NVARCHAR(max) = Stuff((SELECT DISTINCT ',' + Quotename([loop])
FROM TableName
FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
DECLARE @query AS NVARCHAR(max) = 'SELECT *
FROM TableName
PIVOT(Max([total])
FOR [loop] IN ('+ @cols +') ) pvt';
EXECUTE(@query)
在线演示：http ://www.sqlfiddle.com/#!18/ 164a4/3/0
+------+-------+-------+-------+-------+
| year | month | loop1 | loop2 | loop3 |
+------+-------+-------+-------+-------+
| 2012 | 1 | 20 | 10 | 50 |
| 2012 | 2 | 30 | 5 | 60 |
+------+-------+-------+-------+-------+
602392714
清零编程群