我正在尝试转置数据。列数不是固定的(即选定的 ShiftNames 不是固定的(。这是我的输入数据。
Date_time ShiftName Consumption
28-07-2016 Shift 1 20
28-07-2016 Shift 2 21
28-07-2016 Shift 3 22
29-07-2016 Shift 1 30
29-07-2016 Shift 2 31
29-07-2016 Shift 3 32
30-07-2016 Shift 1 40
30-07-2016 Shift 2 41
30-07-2016 Shift 3 42
输出将是这样的
Shift 1 Shift 2 Shift 3 Date_Time
20 21 23 28-07-2016
30 31 32 29-07-2016
40 41 42 30-07-2016
您可以使用透视来执行此操作。下面是一个示例:
测试数据:
DECLARE @temp TABLE(Date_time varchar(100), ShiftName VARCHAR(100), Consumption INT)
INSERT INTO @temp
VALUES
('28-07-2016','Shift 1',20),
('28-07-2016','Shift 2',21),
('28-07-2016','Shift 3',22),
('29-07-2016','Shift 1',30),
('29-07-2016','Shift 2',31),
('29-07-2016','Shift 3',32),
('30-07-2016','Shift 1',40),
('30-07-2016','Shift 2',41),
('30-07-2016','Shift 3',42)
支点:
SELECT
*
FROM
(
SELECT
Date_time,
ShiftName,
Consumption
FROM
@temp
) AS sourceTable
PIVOT
(
SUM(Consumption)
FOR ShiftName IN ([Shift 1],[Shift 2],[Shift 3])
) AS pvt
结果:
Date_time Shift 1 Shift 2 Shift 3
28-07-2016 20 21 22
29-07-2016 30 31 32
30-07-2016 40 41 42
参考:
- 使用 PIVOT 和 UNPIVOT
由于 shiftName 是动态的,请使用此动态查询
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
SELECT
@cols = STUFF((SELECT distinct ',' + QUOTENAME(ShiftName )
FROM
#temp
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @query = 'SELECT * FROM
(
SELECT * FROM #temp
) x
PIVOT
(
Sum(consumption)
FOR ShiftName IN (' + @cols + ')
) p '
EXECUTE(@query);