Pivot SQL table with dynamic year columns

Question

I'm having trouble figuring this out. I've checked similar posts but they only have one column as pivoted as a row. While I need to pivot

I have the following query:

SELECT  
      Year([Date]) as Year
      ,SUM([Drop]) as [Drop]
      ,SUM([TicketsDistributed]) as [TicketsDistributed]
      ,SUM([TicketsSold]) as [TicketsSold]
      ,SUM([GrossTickets]) as [GrossTickets]
      ,SUM([GrossTickets])/SUM(TicketsSold) as 'Per Cap'
  FROM [dbo].[Tickets]
  group by [Date]

Which give me this result:

Year    Drop    TicketsDistributed  TicketsSold GrossTickets    Per Cap
2016    222          100                 5000     4000.00       0.800000
2015    222          110                 5000     4000.00       0.900000
2014    222          120                 5000     4000.00       1.00000

And I would like the following:

                       2016   2015   2014   
Drop                   222    222    222    
TicketsDistributed     100    110    120    
TicketsSold            5000   5000   5000     
GrossTickets           4000   4000   4000
Per Cap                0.8     0.9   1

Based on the suggested answer this is what I have so far but it's not working

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Year(Date)) 
                    from [dbo].[SpringTrainings] t
                    cross apply
                    (
                        select    SUM([Drop]) as [Drop]
      ,SUM([TicketsDistributed]) as [TicketsDistributed]
      ,SUM([TicketsSold]) as [TicketsSold]
      ,SUM([GrossTickets]) as [GrossTickets]
      ,SUM([GrossTickets])/SUM(TicketsSold) as PerCap
  FROM [dbo].[SpringTrainings]
                    ) c ([Drop],[TicketsDistributed],[TicketsSold],[GrossTickets],PerCap)
                    group by Year(Date)
                    order by Year(Date)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')




set @query = 'SELECT [Drop],[TicketsDistributed],[TicketsSold],[GrossTickets],PerCap,' + @cols + ' 
            from 
            (
                select SUM([Drop]) as [Drop]
      ,SUM([TicketsDistributed]) as [TicketsDistributed]
      ,SUM([TicketsSold]) as [TicketsSold]
      ,SUM([GrossTickets]) as [GrossTickets]
      ,SUM([GrossTickets])/SUM(TicketsSold) as PerCap
        FROM [dbo].[SpringTrainings]

            ) x
            pivot 
            (
                max(Year([Date]))
                for Year([Date]) in (' + @cols + ')
            ) p '

execute sp_executesql @query;

SQL is NOT not well suited to this need, how are you using this? in a report? via html? — Paul Maxwell, Mar 05 '16 at 03:19

score 2 · Accepted Answer · edited Mar 05 '16 at 09:53

If you wish to keep using the pivot operator within T-SQL, then first you need to "unpivot" your existing query so you have Year, Label, and Value. While there is an unpivot operator in T-SQL personally I find using CROSS APPLY and VALUES to be much simpler and equally as fast (for more on his approach read this article by Brad Schultz), I particularly like it because I can visualize the result easily by the way I layout the value pairs.

SELECT
      d.Year
    , a.label
    , a.value
FROM (
      SELECT
            YEAR([Date]) AS [Year]
          , SUM([Drop]) AS [Drop]
          , SUM([TicketsDistributed]) AS [TicketsDistributed]
          , SUM([TicketsSold]) AS [TicketsSold]
          , SUM([GrossTickets]) AS [GrossTickets]
          , SUM([GrossTickets]) / SUM(TicketsSold) AS [PerCap]
      FROM [dbo].[Tickets]
      GROUP BY
            [Year]
      ) AS t
      CROSS APPLY ( /* now transform into 5 rows per year but just 1 value column */
          VALUES
                 ('Drop',t.Drop)
               , ('TicketsDistributed',t.TicketsDistributed)
               , ('TicketsSold',t.TicketsSold)
               , ('GrossTickets',t.GrossTickets)
               , ('PerCap',t.PerCap)
      ) AS a (label, value)

That query (above) replaces the derived table x in your dynamic SQL. Once the data has been massaged into that form the pivot looks way simpler:

       ) x
        pivot 
        (
            max([x.Value])
            for [x.Year] in ([2014],[2015],[2016])
        ) p

For your @cols I would suggest something simple like this:

SELECT DISTINCT
    QUOTENAME(Year([date])) 
FROM [dbo].[Tickets]

TIP: if you need a way to order the rows, include that in the cross apply too, like this:

      CROSS APPLY ( /* now transform into 5 rows per year but just 1 value column */
          VALUES
                 (1, 'Drop',t.Drop)
               , (2, 'TicketsDistributed',t.TicketsDistributed)
               , (3, 'TicketsSold',t.TicketsSold)
               , (4, 'GrossTickets',t.GrossTickets)
               , (5, 'PerCap',t.PerCap)
      ) AS a (row_order, label, value)

and then that [row_order] can be used after the pivot is performed.

So the overall could look like this:

DECLARE @cols AS nvarchar(max)
      , @query AS nvarchar(max)

SELECT @cols = STUFF((
            SELECT DISTINCT
                  ',' + QUOTENAME(YEAR([date]))
            FROM [dbo].[Tickets]
            FOR xml PATH (''), TYPE
      )
      .value('.', 'NVARCHAR(MAX)')
      , 1, 1, '')


SET @query = 'SELECT [Year], [Label], '
            + @cols 
            + ' FROM (
                  SELECT
                        d.Year
                      , a.label
                      , a.value
                  FROM (
                        SELECT
                              YEAR([Date]) AS [Year]
                            , SUM([Drop]) AS [Drop]
                            , SUM([TicketsDistributed]) AS [TicketsDistributed]
                            , SUM([TicketsSold]) AS [TicketsSold]
                            , SUM([GrossTickets]) AS [GrossTickets]
                            , SUM([GrossTickets]) / SUM(TicketsSold) AS [PerCap]
                        FROM [dbo].[Tickets]
                        GROUP BY
                              [Year]
                  ) AS d
                        CROSS APPLY (
                                  VALUES
                                         (1,''Drop'',t.Drop)
                                       , (2,''TicketsDistributed'',t.TicketsDistributed)
                                       , (3,''TicketsSold'',t.TicketsSold)
                                       , (4,''GrossTickets'',t.GrossTickets)
                                       , (5,''PerCap'',t.PerCap)
                        ) AS a (row_order,label,value)
                ) x
            pivot 
            (
                max([x.Value])
                for [x.Year] in (' + @cols + ')
            ) p
            ORDER BY [row_order]'

EXECUTE sp_executesql @query;

Pivot SQL table with dynamic year columns

1 Answers1