How can I transpose a sql result like in excel's pivot function?

Question

I'm using the following query in 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

The result is as follows:

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

What I need looks like this - foreach period (rows) I'm interested in the aggregated volumes per mode (column):

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

How can this be accomplished via the Pivot function in MSSQL 2012? Is it possible to get the columns (modes) dynamically and not to define them in the query itself?

You need to use Dynamic SQL, there are loads of examples all over the internet and on here: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query http://stackoverflow.com/questions/7822004/pivots-with-dynamic-columns-in-sql-server — GarethD, Apr 15 '13 at 13:31

score 2 · Accepted Answer · edited May 23 '17 at 12:04

Converted from here

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

score 1 · Answer 2 · answered Apr 15 '13 at 13:54

1

The T-SQL keyword pivot should do what you need. Have a look at http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx (this documentation is for 2008 R2, but it works the same way in SQL Server 2012).

I haven't tested it, but basically, this query should do the trick:

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;

answered Apr 15 '13 at 13:54

Dan

10,480
23
49

I think this misses the crux of the question: **Is it possible to get the columns (modes) dynamically and not to define them in the query itself?** – GarethD Apr 15 '13 at 14:04
Whoops, I must've missed that part of the question. As you're correctly pointing out, my proposed solution requires the columns to be statically defined in the query. The solution, as others have pointed out, is to use the pivot statement in a dynamically built query. – Dan Apr 15 '13 at 14:41

How can I transpose a sql result like in excel's pivot function?

2 Answers2