Convert Rows into columns

Question

I got a table values as bellow.

CREATE TABLE #temp3 (ID INT,Date DATETIME,totalcount INT)

id		date 	totalcount
5	2011-07-30 	2
5	2011-07-16 	1
5	2011-07-23 	2
77	2011-07-16 	1
77	2011-08-28 	1
77	2011-07-10 	1
152	2011-07-03 	1

And I want the output as 

id 2011-07-30 	2011-07-16 2011-07-23 2011-08-28 2011-07-10 2011-07-03
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:[e-mail]

Answer 1

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 ([2011-07-03],[2011-07-10],[2011-07-16],[2011-07-23],[2011-07-30],[2011-08-28])) Pvt

 

---

Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.

Answer 2

Thanks dbp2784 for quick reply..

 

  I tried to use PIVOT but I am getting a error "Incorrect syntax near 'PIVOT'."  Is PIVOT supported in Microsoft SQL Server  2000? I am not sure about this..

---

Thank You, Naveen email:[e-mail]

Answer 3

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) = '2011-07-03' THEN TotalCount else 0 END) AS '2011-07-03',
SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '2011-07-10' THEN TotalCount else 0 END) AS '2011-07-10',
SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '2011-07-16' THEN TotalCount else 0 END) AS '2011-07-16',
SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '2011-07-23' THEN TotalCount else 0 END) AS '2011-07-23',
SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '2011-07-30' THEN TotalCount else 0 END) AS '2011-07-30',
SUM(CASE WHEN CONVERT(VARCHAR(10),Date,121) = '2011-08-28' THEN TotalCount else 0 END) AS '2011-08-28'
FROM #temp3 
GROUP BY ID

 

---

Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.

Answer 4

dbp2784 I can't write as you mentioned. Because as I told earlier the dates are dynamic.They are not static.

Any other options?

 

---

Thank You, Naveen email:[e-mail]

Answer 5

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

Answer 6

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:[e-mail]

Answer 7

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.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/

I suggest to go from there.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

You may want to check this:

Create Table #temp3 (ID INT,Date_ DATETIME,totalcount INT)
Insert Into #temp3 
Select 5, '2011-07-30', 2 Union All 
Select 5, '2011-07-16', 1 Union All 
Select 5, '2011-07-23', 2 Union All 
Select 77, '2011-07-16', 1 Union All 
Select 77, '2011-08-28', 1 Union All 
Select 77, '2011-07-10', 1 Union All 
Select 152, '2011-07-03', 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.

Answer 9

Thank you all..

 

I have generated dynamic Select statement and that solved my prob..

---

Thank You, Naveen email:[e-mail]