Making columns from Row Data
-
Hi,
I have a different issue which I am not sure it is possible in SQL Server.
I have a table called DailySiteCount. In that table I have four columns which are ReportName,SiteName,SiteCount and ReportDate respectively. ReportName and SiteName columns are varchar, SiteCount is integer and ReportDate is DateTime column.
The data stored in this table are as follows:
No_Issue_Level_Info | BLR | 11 | 2006-10-10 14:56:43.670
No_Issue_Level_Info | SGP | 1 | 2006-10-10 14:56:48.530
No_Issue_Level_Info |TIV | 4 |2006-10-10 14:56:48.530
Pending_More_Than_9M | WPL |1 |2006-10-10 14:56:48.543
Pending_More_Than_9M |TIV |121 | 2006-10-10 14:56:48.560
Pending_More_Than_9M |BLR |548 | 2006-10-10 14:56:48.560
Pending_More_Than_9M |SGP |80 |2006-10-10 14:56:48.560
I want to display the data in rows as columns, means don't want to repeat the Report Name and SiteName. I want to display the report as follows:
ReportName BLR SGP TIV WPL TOTAL
No_Issue_Level_Info 11 1 4 0 16
Pending_More_Than_9M 548 80 121 1 750
Is it possible to do like this in Query Analyser?
If possible, please send me the code also. Any help in this regard is great.
Thank you very much.
Raja Raman S:
-
This kind of vertical to horizontal rotation has no generic sql solution. It can be done for specific situations using case statements. Thus your data can be handled by the following select statement:
select ReportName,
sum(case when SiteName='BLR' then SiteCount else 0 end) blr,
sum(case when SiteName='SGP' then SiteCount else 0 end) sgp,
sum(case when SiteName='TIV' then SiteCount else 0 end) tiv,
sum(case when SiteName='WPL' then SiteCount else 0 end) wpl,
sum(SiteCount) total
from DailySiteCount
group by ReportName
order by ReportName
You could make this generic via a function that returns a table which first determines all occurences of SiteName and then dynamically constructs the above sequence of case statements...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply