sql непрерывные значения столбца из данных для просмотра сводной таблицы

У меня есть данные в таблице в этой структуре:

Region | Date | Value A | 01/01/2014 | 100 A | 01/20/2014 | 50 A | 01/02/2014 | 200 A | 01/05/2014 | 300 B | 01/01/2014 | 50 B | 02/15/2014 | 70 B | 02/25/2014 | 50 C | 05/02/2014 | 70 

Я пытаюсь создать такое сводное представление, используя T-SQL-запросы:

 Region | Jan-2014 | Feb-2014 | Mar-014 | Apr-2014 | May-2014 | -> thru desired month-year A | 150 | 200 | 0 | 0 | 300 | B | 50 | 120 | 0 | 0 | 0 | C | 0 | 0 | 0 | 0 | 70 | 

Обратите внимание, что несколько значений в том же месяце для данного региона должны быть агрегированы. Месяцы, у которых нет записей, все равно должны отображаться как столбцы с нулевыми значениями (пример: март и апрель)

Я устал, используя варианты поворота, прокручивая вверх и т. Д., Но, похоже, не может заставить его работать.

Спасибо.

Причина, по которой у вас нет результатов за эти месяцы, заключается в том, что вам не хватает дат, которые вы поворачиваете в столбцы.

Есть несколько способов, которыми вы можете это сделать. Вы можете жестко закодировать все значения даты в части IN вашего запроса, чтобы появлялись столбцы:

 select Region, isnull([Jan-2014], 0) [Jan-2014], isnull([Feb-2014], 0) [Feb-2014], isnull([Mar-2014], 0) [Mar-2014], isnull([Apr-2014], 0) [Apr-2014], isnull([May-2014], 0) [May-2014], isnull([Jun-2014], 0) [Jun-2014], isnull([Jul-2014], 0) [Jul-2014], isnull([Aug-2014], 0) [Aug-2014], isnull([Sep-2014], 0) [Sep-2014], isnull([Oct-2014], 0) [Oct-2014], isnull([Nov-2014], 0) [Nov-2014], isnull([Dec-2014], 0) [Dec-2014] from ( select left(datename(month, t.date), 3) +'-' + cast(year(t.date) as char(4)) monthYear, t.region, t.value from yt t ) src pivot ( sum(value) for monthYear in ([Jan-2014], [Feb-2014], [Mar-2014], [Apr-2014], [May-2014], [Jun-2014], [Jul-2014], [Aug-2014], [Sep-2014], [Oct-2014], [Nov-2014], [Dec-2014]) ) piv; 

См. SQL Fiddle with Demo .

Другой способ сделать это – создать таблицу дат, которую вы можете использовать в своем запросе. После создания вы можете использовать LEFT JOIN для своей таблицы, чтобы вернуть все даты, которые вы хотите отобразить. Вы можете создать рекурсивный запрос для создания этого списка в вашем запросе PIVOT или заполнить таблицу списком дат. Рекурсивный запрос будет похож на этот:

 ;with dates (startDate, endDate) as ( select min(date), cast('2014-12-31' as date) from yt union all select dateadd(m, 1, startDate), enddate from dates where month(startDate) + 1 <= month(enddate) ) select startDate from dates; 

См. SQL Fiddle with Demo . Если вы присоедините это к вашему PIVOT, запрос будет выглядеть следующим образом:

 select * from ( select left(datename(month, d.startdate), 3) +'-' + cast(year(d.startdate) as char(4)) monthYear, t.region, t.value from dates d left join yt t on month(d.startdate) = month(t.date) and year(d.startdate) = year(t.date) ) src pivot ( sum(value) for monthYear in ([Jan-2014], [Feb-2014], [Mar-2014], [Apr-2014], [May-2014], [Jun-2014], [Jul-2014], [Aug-2014], [Sep-2014], [Oct-2014], [Nov-2014], [Dec-2014]) ) piv where region is not null; 

См. SQL Fiddle with Demo . Вы можете использовать это как часть своего запроса PIVOT, но вам все равно придется вручную указывать все даты в предложении IN для PIVOT.

Я предполагаю, что вы хотите использовать динамическую SQL-версию этого запроса, чтобы даты изменились в зависимости от ваших потребностей. Динамическая версия будет:

 DECLARE @cols AS NVARCHAR(MAX), @colsNull AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @startdate datetime = '2014-01-01', @enddate datetime = '2014-12-01' ;with dates (startDate, endDate) as ( select @startdate, @enddate from yt union all select dateadd(m, 1, startDate), enddate from dates where dateadd(m, 1, startDate) <= enddate ) select @cols = STUFF((SELECT ',' + QUOTENAME(left(datename(month, d.startdate), 3) +'-' + cast(year(d.startdate) as char(4))) from dates d -- where startdate >= '2014-01-01' and startdate <= '2014-06-01' group by d.startdate order by d.startdate FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,''), @colsNull = STUFF((SELECT ', isnull(' + QUOTENAME(left(datename(month, d.startdate), 3) +'-' + cast(year(d.startdate) as char(4)))+', 0) as '+QUOTENAME(left(datename(month, d.startdate), 3) +'-' + cast(year(d.startdate) as char(4))) from dates d -- where startdate >= '2014-01-01' and startdate <= '2014-06-01' group by d.startdate order by d.startdate FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') from dates set @query = 'SELECT region, ' + @colsNull + ' from ( select left(datename(month, t.date), 3) +''-'' + cast(year(t.date) as char(4)) monthYear, t.region, t.value from yt t ) x pivot ( sum(value) for monthyear in (' + @cols + ') ) p ' execute(@query); 

См. SQL Fiddle with Demo . Эта версия использует рекурсивный CTE для генерации списка дат, которые будут использоваться в динамической строке sql. Несмотря на то, что ваша таблица данных может отсутствовать в течение отображаемых месяцев, у вас все равно будет новый столбец.

Это дает результат:

 | REGION | JAN-2014 | FEB-2014 | MAR-2014 | APR-2014 | MAY-2014 | JUN-2014 | JUL-2014 | AUG-2014 | SEP-2014 | OCT-2014 | NOV-2014 | DEC-2014 | ---------------------------------------------------------------------------------------------------------------------------------------------- | A | 650 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | B | 50 | 120 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | C | 0 | 0 | 0 | 0 | 70 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 
  • Динамические Unpivot и разделенные столбцы SQL Server 2012
  • PIVOT с дополнительным столбцом «else / default / fallback»?
  • Строки таблицы SQL в столбцы - возможно ли PIVOT?
  • Запрос для перечисления каждой дочерней записи в столбцах родительского элемента
  • Ошибка синтаксиса в запросе динамического SQL-сервера с оговоркой о свопинге
  • Запрос с строками в столбцы
  • Как превратить столбцы в строки
  • T-SQL: от строк до столбцов, но не фактический стержень
  • Горизонтальный столбец запроса
  • SQL pivot не обрабатывает отсутствие данных
  • Значение CSV в сводном запросе
  • Давайте будем гением компьютера.