如何像在 excel 的透视函数中那样转置 sql 结果？

Question

我在MSSQL 2012中使用以下查询:

declare @startTime int=0*3600;
declare @endTime int=30*3600;
declare @link varchar(10)='RO2566a';
declare @period int=3600; 
SELECT (B.time-@startTime)/@period as periodStart, B.link, B.mode, count(*) as Volume
  FROM
       (
       Select A.*, 
             case when A.vehicle='' then 'walk' 
                  when A.line is NULL then 'car'
                    else 'pt' end mode
             FROM 
             (Select E.time, E.link, E.vehicle, E.person, T.transitLineId as line, T.transitRouteId as route, T.departureId, T.time as DepartureTime
                    FROM (Select * From evtEnteredLink  where link=@link and time between @startTime and @endTime) E
                 LEFT OUTER JOIN evtTransitDriverStarts T on (E.vehicle=T.vehicleId) 
             ) A
             where A.time between @startTime and @endTime
       )B
       group by B.link, B.mode, (B.time-@startTime)/@period

结果如下:

periodStart mode    Volume
0       pt  19
1       pt  24
2       pt  24
3       car 4
3       pt  25
4       car 64
4       pt  27

我需要的是这样的—对于每个周期(行)，我对每个模式(列)的聚合卷感兴趣:

PeriodStart car pt  
0               19
1               24
2               24
3           4   25
4           64  27

如何在MSSQL 2012中通过Pivot函数实现这一点?是否有可能动态获取列(模式)，而不是在查询本身中定义它们?

Answer 1

从这里转换

create table temp
(
    periodstart int,
    mode varchar(3),
    volume int
)
insert into temp values (0,'pt',19);
insert into temp values (1,'pt',24);
insert into temp values (2,'pt',24);
insert into temp values (3,'car',4);
insert into temp values (3,'pt',25);
insert into temp values (4,'car',64);
insert into temp values (4,'pt',27);

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.mode) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @query = 'SELECT periodstart, ' + @cols + ' from 
            (
                select periodstart
                    , volume
                    , mode
                from temp
           ) x
            pivot 
            (
                 max(volume)
                for mode in (' + @cols + ')
            ) p '

execute(@query)
drop table temp

Answer 2

T-SQL关键字pivot应该可以满足您的需要。请查看http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx(此文档适用于2008 R2，但在SQL Server 2012中工作方式相同)。

我还没有测试过，但基本上，这个查询应该可以做到:

SELECT PeriodStart, [car], [pt]
FROM 
(
    SELECT ... -- Insert the entire SELECT statement from your question here.
) p
PIVOT
(
    SUM(Volume)  -- Or MAX(), COUNT(), or whichever aggregate function suits your need.
    FOR Mode IN ( [car], [pt] )
) AS pvt
ORDER BY pvt.PeriodStart;