SQL How to PIVOT table using common table expression and dynamic SQL

Question

I have this SQL query which groups searches by month:

; with Mth (st, nd) as (
    select DATEADD (M, datediff (m, 0,'2012-09-01'), 0),
            DATEADD (M, DATEDIFF (m, 0, '2012-09-01') + 1, 0)  
    union all
    select DATEADD (m, 1, st),
            DATEADD (m, 1, nd)
    from Mth
    where nd <= DATEADD (m, datediff (m, 0, getdate()), 0)
)
select MONTH(Mth.st) Month,
    COUNT(S.QRY_ID) Searches
FROM Mth
LEFT JOIN SEARCHES S
on Mth.st <= S.CREATED
and Mth.nd > S.CREATED
GROUP BY YEAR(Mth.st), MONTH(Mth.st)
ORDER BY 1,2

The result appears like this:

 Month  |  Searches
---------------------
   9    |    21
   10   |    32
   11   |    18

I'm trying to figure out how to use PIVOT to achieve this:

  9   |   10    |   11
-----------------------
  21  |   32    |   18

Could anyone please explain to me how to do it?

Answer 1

You can use something like this:

; with Mth (st, nd) as (
    select DATEADD (M, datediff (m, 0,'2012-09-01'), 0),
            DATEADD (M, DATEDIFF (m, 0, '2012-09-01') + 1, 0)  
    union all
    select DATEADD (m, 1, st),
            DATEADD (m, 1, nd)
    from Mth
    where nd <= DATEADD (m, datediff (m, 0, getdate()), 0)
)
select *
from
(
  select MONTH(Mth.st) Month,
      COUNT(S.QRY_ID) Searches
  FROM Mth
  LEFT JOIN SEARCHES S
    on Mth.st <= S.CREATED
    and Mth.nd > S.CREATED
  GROUP BY YEAR(Mth.st), MONTH(Mth.st)
) src
pivot
(
  sum(searches)
  for month in ([9], [10], [11])
) piv

See SQL Fiddle with Demo

If you have an unknown number of months to transform, then you can use dynamic sql similar to this:

; with Mth (st, nd) as (
    select DATEADD (M, datediff (m, 0,'2012-09-01'), 0),
            DATEADD (M, DATEDIFF (m, 0, '2012-09-01') + 1, 0)  
    union all
    select DATEADD (m, 1, st),
            DATEADD (m, 1, nd)
    from Mth
    where nd <= DATEADD (m, datediff (m, 0, getdate()), 0)
)
select *
into #dates
from Mth

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

select @cols = STUFF((SELECT  ',' + QUOTENAME(month(st))
                    from #dates
                    group by month(st)
                    order by month(st) 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = ' with Mth (st, nd) as (
                select DATEADD (M, datediff (m, 0,''2012-09-01''), 0),
                        DATEADD (M, DATEDIFF (m, 0, ''2012-09-01'') + 1, 0)  
                union all
                select DATEADD (m, 1, st),
                        DATEADD (m, 1, nd)
                from Mth
                where nd <= DATEADD (m, datediff (m, 0, getdate()), 0)
            )
            select *
            from
            (
              select MONTH(Mth.st) Month,
                  COUNT(S.QRY_ID) Searches
              FROM Mth
              LEFT JOIN SEARCHES S
                on Mth.st <= S.CREATED
                and Mth.nd > S.CREATED
              GROUP BY YEAR(Mth.st), MONTH(Mth.st)
            ) src
            pivot
            (
              sum(searches)
              for month in ('+@cols+')
            ) piv'

execute(@query)

See SQL Fiddle with Demo