如何以特定格式转置SQL中的表?

Question

我需要按以下格式转置 SQL 表。数据显示应按日期分组，然后按时间分组，并显示与时间和日期对应的值。

从：

Datetime            | Value 1 | Value 2| Value 3
------------------------------------------------
2018-01-11 00:01:00 | 1.22    | 2.22   | 34.12
2018-01-11 00:02:00 | 2.22    | 3.22   | 43.12
2018-01-11 00:03:00 | 1.22    | 2.22   | 54.12
2018-01-11 23:58:00 | 1.22    | 2.22   | 34.12
2018-01-11 23:59:00 | 1.22    | 2.22   | 34.12

自：

|    2018-01-11            |     2018-01-12           |
Time     | Value 1| Value 2| Value 3| Value 1| Value 2| Value 3|
----------------------------------------------------------------    
00:01:00 | 1.22   | 2.22   | 34.12  | 1.22   | 2.22   | 34.12  |
00:02:00 | 2.22   | 3.22   | 54.13  | 2.33   | 4.32   | 54.98  |
23:58:00 | 2.22   | 3.22   | 54.13  | 2.33   | 4.32  | 54.98   |
23:59:00 | 2.22   | 3.22   | 54.13  | 2.33   | 4.32  | 54.98   |

Answer 1

这可以在 sqlserver 中完成，如下所示： 它是动态数据透视表和临时表创建的组合。可能有更快的方法，但这有效。

首先创建示例数据：

create table #t(dt datetime, v1 decimal(5,2), v2 decimal(5,2), v3 decimal(5,2))
insert #t values 
('2018-01-11 00:01:00',1.22,2.22,34.12)
,('2018-01-11 00:02:00',2.22,3.22,43.12)
,('2018-01-11 00:03:00',1.22,2.22,54.12)
,('2018-01-11 23:58:00',1.22,2.22,34.12)
,('2018-01-11 23:59:00',1.22,2.22,34.12)
,('2018-01-12 00:01:00',1.55,5.55,34.15)
,('2018-01-12 00:02:00',5.55,3.55,43.15)
,('2018-01-12 00:03:00',1.55,5.55,54.15)
,('2018-01-12 23:58:00',1.55,5.55,34.15)
,('2018-01-12 23:59:00',1.55,5.55,34.15)

然后将列转置为行：

create table #t2(t varchar(5), dv varchar(13), v decimal(5,2))
insert #t2
select convert(varchar(5),convert(time,dt)) as t,
convert(varchar(10),convert(date,dt))+' ' + v as dv,
case when v='v1' then v1 when v='v2' then v2 when v='v3' then v3 end as v
from #t
cross join (values ('v1'),('v2'),('v3')) as v(v)

然后计算出列列表(将日期和值合并到列名中(

DECLARE @cols AS VARCHAR(MAX)
select @cols=STUFF((SELECT distinct ',' + QUOTENAME(dv) 
FROM #t2 c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')

然后基于这些列创建动态查询

DECLARE @query AS VARCHAR(MAX);
set @query = 'SELECT t, ' + @cols + ' from 
(
select t, dv, v
from #t2
) x
pivot 
(
max(v)
for dv in (' + @cols + ')
) p '
execute(@query) 

结果：(列实际上是对齐的！

t   2018-01-11 v1   2018-01-11 v2   2018-01-11 v3   2018-01-12 v1   2018-01-12 v2   2018-01-12 v3
00:01   1.22    2.22    34.12   1.55    5.55    34.15
00:02   2.22    3.22    43.12   5.55    3.55    43.15
00:03   1.22    2.22    54.12   1.55    5.55    54.15
23:58   1.22    2.22    34.12   1.55    5.55    34.15
23:59   1.22    2.22    34.12   1.55    5.55    34.15