分解表以透视列（SQL，PYSPARK）

分解表以透视列（SQL，PYSPARK）

表脚本和样本数据

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 |+------+-------+-------+-------+-------+