Transform vertical result into horizontal mode (T-SQL)

Question

Here are the sample data :

CalculationDatePLResult
2014-01-02 100
2014-01-03 200
2014-02-03 300
2014-02-04 400
2014-02-27 500

Here are the expected result (in logical format) :

January February
CalculationDatePLResultCalculationDatePLResult
2014-01-02 100 2014-02-03 300
2014-01-03 200 2014-02-04 400
2014-02-27 500

Here are the expected result (using T-SQL Query) :

Jan-CalculationDateJan-PLResultFeb-CalculationDateFeb-PLResult
2014-01-02 100 2014-02-03 300
2014-01-03 200 2014-02-04 400
2014-02-27 500

Objective:

Classify the result according to the month. In the above example, the January's results are placed in the January breakdown.
The number of months can be dynamic. In the above example, it only shows January and February because there are only results for 2 months
The result will be displayed through Excel. Actually I can query multiple query tables to aggregate the result across different months, but if it's possible to return all the result through one single query, then it will be easier to be maintained and debugged.

Here are the scripts to populate the sample data :

CREATE TABLE #PLResultPerDay ( CalculationDate DATETIME, PLResult DECIMAL(18,8) )
INSERT INTO #PLResultPerDay ( CalculationDate, PLResult ) VALUES ('2014-01-02' , 100 )
INSERT INTO #PLResultPerDay ( CalculationDate, PLResult ) VALUES ('2014-01-03' , 200 )
INSERT INTO #PLResultPerDay ( CalculationDate, PLResult ) VALUES ('2014-02-03' , 300 )
INSERT INTO #PLResultPerDay ( CalculationDate, PLResult ) VALUES ('2014-02-04' , 400 )

So far here is my attempt in building the query :

SELECT 
    CalculationDate, [January], CalculationDate, [February]
FROM 
(
    SELECT CalculationDate, PLResult, DATENAME(MONTH, CalculationDate) AS [MTH]
    FROM #PLResultPerDay
) x
PIVOT
( 
    MIN(PLResult)
    FOR [MTH] IN ([January], [February])
) p

Transform vertical result into horizontal mode (T-SQL)

1 Answers