MS SQL server Pivot Tables

[Baz]

Trying to pivot a lot of data in SQL server, and have been playing about with Pivot tables.... Problem is, the data I want to pivot is not always numbers, its often text.

The statement I am using is...

Select [i]Column1[/i],
	 SUM(CASE [i]Column2 [/i]WHEN '[i]text1[/i]' THEN [i]Column3 [/i]ELSE 0 END) AS '[i]text1[/i]',
	 SUm(CASE [i]Column2 [/i]WHEN '[i]text2[/i]' THEN [i]Column3 [/i]ELSE 0 END) AS '[i]text2[/i]'
FROM [i]Table [/i]where [i]ID[/i] = ID
Group By [i]Column1[/i]

But it falls over if I have text in there..

Is there a way of doing a pivot in SQL with Text...

 

[oneilldo]

could you convert it to varchar data, shouldnt the else also be an empty string instead of the number 0?

Select Column1,
SUM(CASE CONVERT(VARCHAR,Column2) WHEN 'text1' THEN CONVERT(VARCHAR,Column3) ELSE '' END) AS 'text1',
etc.

 

[PsychoDuck]

Sql server 2005 has built in pivot functionality, otherwise look at this article http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx which works extremely well.