Creating Sql Table With Range Of Months As Columns And Populating Fields With Table Data

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 w

Solution 1:

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

Solution 2:

CREATETABLE #temp(Project# varchar(25), Months varchar(25),MonthValues int)
INSERTINTO #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

DROPTABLE #temp