创建以月份范围为列的 SQL 表并使用表数据填充字段 [英] Creating SQL table with range of months as columns and populating fields with table data

问题描述

我需要创建一个 SQL 表或视图，以便为两个日期范围内的每个月生成列.因此，01/01/2014 - 04/01/2014 的范围将生成四列标题为 2014 年 1 月、2014 年 2 月、2014 年 3 月和 2014 年 4 月的列.我还需要它用与这些月份相关的数据填充字段下表，而任何没有适当数据的字段都将填充为空值:

I need to create an SQL table or view that would generate columns for every month between a range of two dates. So a range of 01/01/2014 - 04/01/2014 would generate four columns with headers Jan 2014, Feb 2014, March 2014, April 2014. I also need it to populate the fields with data that pertain to those months from the following table, while any field with no appropriate data would be filled in with null values:

------------------------------------------------------
|     Project#    |    Months    |    MonthValues    |
------------------------------------------------------
|       001       |   Jan 2014   |        1          |
------------------------------------------------------
|       002       |   Feb 2014   |        2          |
------------------------------------------------------

这应该创建表:

---------------------------------------------------------------------------------------
|     Project#    |    Jan 2014    |    Feb 2014    |   March 2014   |   April 2014   |
---------------------------------------------------------------------------------------
|       001       |       1        |      null      |      null      |      null      |
---------------------------------------------------------------------------------------
|       001       |      null      |       2        |      null      |      null      |
---------------------------------------------------------------------------------------

使用 SQL Server 2008.

Using SQL server 2008.

在没有任何运气的情况下一直试图解决这个问题，任何帮助将不胜感激！

Been trying to get my head around this for sometime without any luck, any help would be much appreciated!

推荐答案

您可以使用 PIVOT函数:

SELECT  Project, [Jan 2014], [Feb 2014], [Mar 2014], [April 2014]
FROM    T
        PIVOT
        (   SUM(MonthValues)
            FOR Months IN ([Jan 2014], [Feb 2014], [Mar 2014], [April 2014])
        ) pvt;

SQL Fiddle 示例

如果您需要能够根据日期改变列数，则需要使用动态 SQL 生成上述查询.关于这一点，实际上有数百个关于此的问题.我认为这个会有所帮助(或者可能是 BlueFeet 的任何一个答案！)

If you need to be able to vary the number of columns based on dates you would need to use Dynamic SQL to generate the above query. There are literally hundreds of questions on SO about this. I think this one will help (Or any one of BlueFeet's answers probably!)

这篇关于创建以月份范围为列的 SQL 表并使用表数据填充字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！