SQL Server - convert rows into colums

Asked By unni krishnan on 21-Dec-10 02:24 AM
User Name Hours Count
andre 9 1
andre 13 4
andre 14 3
andre 15 2
BennySunyoto 10 6
BennySunyoto 11 27
BennySunyoto 12 15
BennySunyoto 13 5

Hi, this is my table structure, i need to convert the hours into column and assign the count
it should look like

9 10 11 12 13 14
andre 1 0 0 0 4 3

The Hour column will be shown between 9 - 20
Anoop S replied to unni krishnan on 21-Dec-10 03:31 AM
You can do this with the help of PIVOT functionality of SQL Sever.
You van refer this for eg
http://p2p.wrox.com/oracle/11931-sql-query-convert-columns-into-rows.html
Web Star replied to unni krishnan on 21-Dec-10 03:38 AM
you can simply use Case for convert rows into columns

SELECT CASE WHEN [RowID] = 1 THEN [Value] ELSE NULL END AS [1],
CASE WHEN [RowID] = 2 THEN [Value] ELSE NULL END AS [2],
CASE WHEN [RowID] = 3 THEN [Value] ELSE NULL END AS [3]
FROM YourTable


SQL Server Helper
http://www.sql-server-helper.com



Pivot Query

Lalitha Kumaran replied to unni krishnan on 21-Dec-10 05:28 AM




select name,
   [9] AS '9',
   [10] AS '10',
   [11] AS '11',
   [12] AS '12',
   [13] AS '13',
   [14] AS '14',
           [15] AS '15'
          
FROM
(
     Select name,Hours,numCount
from  test_query
     ) ps
     PIVOT (
            MAX(numCount)
            For  Hours IN ([9],[10],[11],[12],[13],[14],[15] ) ) AS pvt
order by name




pivot query
Lalitha Kumaran replied to Lalitha Kumaran on 21-Dec-10 05:30 AM

http://eggheadcafe.com/FileUpload/5206786_result.JPG