Creating SQL table with range of months as columns and populating fields with table data

Question

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

This should create the table:

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

Using SQL server 2008.

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

score 2 · Accepted Answer · edited May 23 '17 at 10:31

2

You can use the PIVOT function:

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;

Example on SQL Fiddle

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

edited May 23 '17 at 10:31

Community

1
1

answered Feb 27 '14 at 16:34

GarethD

68,045
10
83
123

Hey, yeah I wanted it to be created dynamically so it looks like I got some reading up to do then, thanks a ton for your help though, the pivot table is a good start. – Lausn Feb 27 '14 at 16:46

score 1 · Answer 2 · answered Feb 27 '14 at 17:06

CREATE  TABLE #temp(Project# varchar(25), Months varchar(25),MonthValues int)
INSERT INTO #temp VALUES
('001','Jan 2014', 1),('002','Feb 2014',2)


Select Project#, [Jan 2014], [Feb 2014]  FROM #temp
Pivot(
Max(MonthValues) 
For Months In ([Jan 2014],[Feb 2014])
) As myPTable

DROP TABLE #temp

Creating SQL table with range of months as columns and populating fields with table data

2 Answers2

Linked