Answered by:
Convert Rows into columns

I got a table values as bellow.
CREATE TABLE #temp3 (ID INT,Date DATETIME,totalcount INT) id date totalcount 5 20110730 2 5 20110716 1 5 20110723 2 77 20110716 1 77 20110828 1 77 20110710 1 152 20110703 1 And I want the output as id 20110730 20110716 20110723 20110828 20110710 20110703 5 2 0 0 0 0 0 77 0 1 0 1 1 0 152 0 0 0 0 0 1
FYI: The rows in #temp3 are dynamic. The dates are not static.
I am using Microsoft SQL Server 2000  (Build 3790: Service Pack 2)Please can anyone help me?
Thank You, Naveen email:naveen_dkt@yahoo.co.in Edited by naveendkt Tuesday, August 30, 2011 5:47 PM
Question
Answers

You may want to check this:
Create Table #temp3 (ID INT,Date_ DATETIME,totalcount INT) Insert Into #temp3 Select 5, '20110730', 2 Union All Select 5, '20110716', 1 Union All Select 5, '20110723', 2 Union All Select 77, '20110716', 1 Union All Select 77, '20110828', 1 Union All Select 77, '20110710', 1 Union All Select 152, '20110703', 1 Declare @myDates Varchar(1000), @SelectedDate DateTime Set @myDates = '' Set @SelectedDate = '01/01/1900' While @SelectedDate <> '01/01/2050' Begin Set @SelectedDate = IsNull((Select Distinct top 1 Date_ From #temp3 Where Date_ > @SelectedDate Order by Date_), '01/01/2050') If @SelectedDate <> '01/01/2050' Begin Set @myDates = @myDates + 'Sum(Case When Date_ = ' + '''' + Convert(Varchar(10), @SelectedDate, 101) + '''' + ' Then TotalCount Else 0 End)' + ' As [' + CONVERT(Varchar(20), @SelectedDate, 101) + '],' End End Set @myDates = 'Select ID, ' + SubString(@myDates, 1, Len(@myDates)  1) + ' From #temp3 Group By ID' Execute @myDates
Best Wishes, Arbi  MCC 2011; Please vote if you find this posting was helpful or Mark it as answered. Marked as answer by naveendkt Tuesday, August 30, 2011 7:47 PM
All replies

You need to use PIVOT operator for this,
SELECT * FROM (SELECT ID, CONVERT(VARCHAR(10), Date,121) Date, totalcount FROM #temp3 ) tbl PIVOT (MIN(totalcount) FOR Date IN ([20110703],[20110710],[20110716],[20110723],[20110730],[20110828])) Pvt
Regards, Deven  Please vote if you find any of my post helpful. 

PIVOT operator is not supported in SQL 2000, You need to use a different method.
SELECT ID, SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '20110703' THEN TotalCount else 0 END) AS '20110703', SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '20110710' THEN TotalCount else 0 END) AS '20110710', SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '20110716' THEN TotalCount else 0 END) AS '20110716', SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '20110723' THEN TotalCount else 0 END) AS '20110723', SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '20110730' THEN TotalCount else 0 END) AS '20110730', SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '20110828' THEN TotalCount else 0 END) AS '20110828' FROM #temp3 GROUP BY ID
Regards, Deven  Please vote if you find any of my post helpful. 

For dynamic dates you obviously need dynamic query. With dynamic PIVOT it's a piece of cake, with dynamic case based query it's slightly more complex  you need to know how to construct this dynamic query.
Take a look at my blog showing several examples of dynamic pivot. I show a bit more complex problem, but your case should fit
Dynamic PIVOT on multiple columns
Update. I reviewed the code and I see I used XML PATH and ROW_NUMBER() function that are part of the SQL Server 2005+
In any case, the idea remains the same and for your case you may want to google on
'DYNAMIC CROSS TAB SQL Server 2000'
For every expert, there is an equal and opposite expert.  Becker's Law
My blog 
Thanks Naomi..
But I can't use PIVOT as my DB is in MSSQL 2000.I came to know PIVOT is introduced in MSSQL 2005.
I looked at the DB property and found compatibility level is 80. I think it will only work in compatibility level 90 and 100
Thank You, Naveen email:naveen_dkt@yahoo.co.in 
Please read carefully what I wrote. For your case you need dynamic case based aggregation. Say, quick search on the above search terms brings
http://www.simpletalk.com/sql/tsqlprogramming/creatingcrosstabqueriesandpivottablesinsql/
I suggest to go from there.
For every expert, there is an equal and opposite expert.  Becker's Law
My blog 
You may want to check this:
Create Table #temp3 (ID INT,Date_ DATETIME,totalcount INT) Insert Into #temp3 Select 5, '20110730', 2 Union All Select 5, '20110716', 1 Union All Select 5, '20110723', 2 Union All Select 77, '20110716', 1 Union All Select 77, '20110828', 1 Union All Select 77, '20110710', 1 Union All Select 152, '20110703', 1 Declare @myDates Varchar(1000), @SelectedDate DateTime Set @myDates = '' Set @SelectedDate = '01/01/1900' While @SelectedDate <> '01/01/2050' Begin Set @SelectedDate = IsNull((Select Distinct top 1 Date_ From #temp3 Where Date_ > @SelectedDate Order by Date_), '01/01/2050') If @SelectedDate <> '01/01/2050' Begin Set @myDates = @myDates + 'Sum(Case When Date_ = ' + '''' + Convert(Varchar(10), @SelectedDate, 101) + '''' + ' Then TotalCount Else 0 End)' + ' As [' + CONVERT(Varchar(20), @SelectedDate, 101) + '],' End End Set @myDates = 'Select ID, ' + SubString(@myDates, 1, Len(@myDates)  1) + ' From #temp3 Group By ID' Execute @myDates
Best Wishes, Arbi  MCC 2011; Please vote if you find this posting was helpful or Mark it as answered. Marked as answer by naveendkt Tuesday, August 30, 2011 7:47 PM
