Saturday, April 14, 2012

Rows to columns in sql server

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

Thursday, January 12, 2012

Task 1 - Horizontal Scroll Bar


Task 2 - min-width (not working in IE 7)