This blog is moved to https://mraufk.wordpress.com/

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

Task 1 - Horizontal Scroll Bar


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

LINQ Video Tutorials 'How Do I in C#'

http://download.microsoft.com/download/0/E/4/0E450D3C-D709-4113-AC74-43A27257E77E/WinVideo-stanfield-linq-to-sql-part1-cs.wmv

http://download.microsoft.com/download/F/D/F/FDFACB01-8315-47BE-BED9-BA04B74EB6FF/WinVideo-stanfield-linq-to-sql-part2.wmv

http://download.microsoft.com/download/3/7/7/37799B79-6F8E-4C52-8B20-AC34A49851E6/WinVideo-stanfield-linq-to-sql-part3-cs.wmv

http://download.microsoft.com/download/F/6/B/F6BD1528-550A-494C-B0B2-B92B20424DCE/WinVideo-stanfield-linq-to-sql-part4-cs.wmv

http://download.microsoft.com/download/9/6/b/96b82db2-5cb5-45d1-b90e-9f2cd45f044a/WinVideo-stanfield-linq-to-sql-part5-cs.wmv

http://download.microsoft.com/download/2/4/6/246d0c13-7236-4054-993e-d4ee0df8c33f/WinVideo-stanfield-linq-to-sql-part6-cs.wmv

http://download.microsoft.com/download/5/b/3/5b3fb889-6baf-48e6-be6a-c5d827604caa/WinVideo-stanfield-linq-to-sql-part7-cs.wmv

http://download.microsoft.com/download/b/a/2/ba27fc07-239d-4f67-b47b-543378ae0016/WinVideo-stanfield-linq-to-sql-part8.wmv

http://download.microsoft.com/download/d/1/9/d19b229b-a267-4c30-9b51-8095ecd9c9b2/WinVideo-stanfield-linq-to-sql-part9-vb.wmv

LINQ Video Tutorials VB

http://download.microsoft.com/download/a/4/c/a4c98be0-30cb-44a4-880d-53a5025cd7b0/1firstlinqqueries.wmv

http://download.microsoft.com/download/a/4/c/a4c98be0-30cb-44a4-880d-53a5025cd7b0/2linqgroupandaggregate.wmv

http://download.microsoft.com/download/a/4/c/a4c98be0-30cb-44a4-880d-53a5025cd7b0/convertvs2005to2008.wmv

http://download.microsoft.com/download/a/4/c/a4c98be0-30cb-44a4-880d-53a5025cd7b0/7startinglinqtoxml.wmv

http://download.microsoft.com/download/a/4/c/a4c98be0-30cb-44a4-880d-53a5025cd7b0/8xmlintellisense.wmv

http://download.microsoft.com/download/a/4/c/a4c98be0-30cb-44a4-880d-53a5025cd7b0/9createlinqtoxml.wmv

http://download.microsoft.com/download/a/4/c/a4c98be0-30cb-44a4-880d-53a5025cd7b0/10excellinqtoxml.wmv

.Net Must Have Tools

http://msdn.microsoft.com/en-us/magazine/cc300497.aspx

iPod Shuffle

When you formatted it, you may have deleted its internal software. Never format iPods from Windows Explorer, Disk Utility, or anything that's not iTunes.

To fix this, launch iTunes, plug in the iPod, and restore its software from there. As this will format the disk, you should back up anything you have put on the iPod in the meantime.

Also remember that you have to use iTunes to put music on the iPod - you can