Great !
I came across to do the following task
Input:-
An excel sheet with contains lakhs of records with the following format
year month holidays
Jack 2010 12 2
Jack 2010 4 4
Jack 2010 2 1
Jack 2010 1 3
Now I want to format the above sheet to list how many holidays an employee took in a month. For example, the output will look like this
emp year Jan Feb Mar Apr... ...Dec
Jack 2010 3 1 0 4 2
What did you get from the above example. Yes I need to convert the rows to columns.
It can be done using simple 'case' statement. It can also be done using 'pivot' tables too.
Here comes the simple technique.
We know the columns are static. So just 'hard-code' everything :)
SELECT
F.Employee
,F.year
,SUM(ISNULL(CASE WHEN F.Month = 1 THEN Fridays END,0)) AS Jan
,SUM(ISNULL(CASE WHEN F.Month = 2 THEN Fridays END,0)) AS Feb
,SUM(ISNULL(CASE WHEN F.Month = 3 THEN Fridays END,0)) AS Mar
,SUM(ISNULL(CASE WHEN F.Month = 4 THEN Fridays END,0)) AS Apr
,SUM(ISNULL(CASE WHEN F.Month = 5 THEN Fridays END,0)) AS May
,SUM(ISNULL(CASE WHEN F.Month = 6 THEN Fridays END,0)) AS Jun
,SUM(ISNULL(CASE WHEN F.Month = 7 THEN Fridays END,0)) AS Jul
,SUM(ISNULL(CASE WHEN F.Month = 8 THEN Fridays END,0)) AS Aug
,SUM(ISNULL(CASE WHEN F.Month = 9 THEN Fridays END,0)) AS Sep
,SUM(ISNULL(CASE WHEN F.Month = 10 THEN Fridays END,0)) AS Oct
,SUM(ISNULL(CASE WHEN F.Month = 11 THEN Fridays END,0)) AS Nov
,SUM(ISNULL(CASE WHEN F.Month = 12 THEN Fridays END,0)) AS DEC
FROM dbo.TableName F
GROUP BY F.Employee, F.year
ORDER BY F.Employee, F.year
Nice na !!! ? :)
