SQL как сделать сводную таблицу с использованием общего табличного выражения и динамического SQL

У меня есть этот SQL-запрос, который группирует поиск по месяцам: 

; 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

Результат выглядит так: 

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

Я пытаюсь понять, как использовать PIVOT для достижения этого: 

  9   |   10    |   11
-----------------------
  21  |   32    |   18
Не мог бы кто-нибудь объяснить мне, как это сделать?
1 2
sql sql-server tsql pivot common-table-expression

1 ответ:

Вы можете использовать что-то вроде этого: 

; 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

Смотрите SQL Fiddle with Demo

Если у вас есть неизвестное количество месяцев для преобразования, то вы можете использовать динамический sql, подобный этому: 

; 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)

Смотрите SQL Fiddle with Demo

5