用于数据透视表视图的数据的sql连续列值

I have data in a table in this structure:

我在这个结构的表中有数据:

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

I am trying to create a pivot view like this using T-SQL queries:

我试图使用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   |

Please note, multiple values in the same month for a given region needs to be aggregated Months that have no records should still show up as columns with zero values (Ex: March and April)

请注意,对于给定区域,同一月份的多个值需要汇总。没有记录的月份仍应显示为零值的列(例如:3月和4月)

I tired using pivot options, withroll up etc., -- but can't seem to get this to work any help is much appreciated..

我厌倦了使用枢轴选项,滚动等等, - 但似乎无法让这个工作任何帮助非常感谢..

Thank you.

1 个解决方案

#1

The reason why you do not have results for those months is because you are missing the dates that you are pivoting into columns.

您没有这几个月的结果的原因是因为您错过了转入列的日期。

There are a few ways that you can do this. You can hard code all of the date values in the IN portion of your query so the columns will appear:

有几种方法可以做到这一点。您可以对查询的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;

See SQL Fiddle with Demo.

请参阅SQL Fiddle with Demo。

Another way to do this is to create a table of dates that you can use in your query. Once created, then you can use a LEFT JOIN to your table so you return all of the dates that you want to appear. You can either create a recursive query to generate this list in your PIVOT query, or populate a table with a list of dates. A recursive query will be similar to this:

另一种方法是创建一个可以在查询中使用的日期表。创建后,您可以对表使用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;

See SQL Fiddle with Demo. If you join this to your PIVOT, then the query will be:

请参阅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;

See SQL Fiddle with Demo. You could use this as a part of your PIVOT query, but you would still need to hand-code all of the dates in the IN clause for the PIVOT.

请参阅SQL Fiddle with Demo。您可以将其用作PIVOT查询的一部分,但您仍需要手动编写PIVOT的IN子句中的所有日期。

I am guessing that you want to use a dynamic SQL version of this query so the dates will change based on your needs. The dynamic version will be:

我猜你想使用这个查询的动态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);

See SQL Fiddle with Demo. This version uses the recursive CTE to generate the list of dates that will be used in the dynamic sql string. Even though your table of data might not exist for the months displayed, you will still have a new column.

请参阅SQL Fiddle with Demo。此版本使用递归CTE生成将在动态sql字符串中使用的日期列表。即使您显示的月份中的数据表可能不存在,您仍然会有一个新列。

This gives a result:

这给出了一个结果:

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