How do I pivot simple repeating data in SQL 2005 that is of type Text

Question

Hello there,

I've done some searching and found info on how to pivot numbers and sum them, but I haven't seen where I can pivot text.

Simplified, we have a table that looks a little something like this...

PlanID--------AreaAbbrev---------AreaDescription----------GoalText

10133---------Rec----------------Recreation-------------------Johnny is good at...

10133---------Community----------Community Part----------Johnny is currently going...

10133---------Employment---------Employment--------------Johnny is currently employed at...

10144---------Rec----------------Recreation------------------Sammy is good at...

10144---------Community----------Community Part--------Sammy is currently going...

10144---------Employment---------Employment------------Sammy is currently employed at...

You can see there's always three different AreaAbbrev and they repeat. AND you can see that the GoalText can be lengthy and is always different.

Here's what I'd like the new Select to look like.

PlanID----------Recreation---------------Community Part------------------Employment

10133----------Johnny is good at..-----Johnny is currently going...----Johnny is currently employeed at...

10144----------Sammy is good at..-----Sammy is currently going...----Sammy is currently employed at...

Sorry for all the dashes... I tried 4 or 5 different ways to display these tables and this was all I could work out. I had to use the dashes as spaces to get it lined up.

Any ideas?

Thanks! Bill

Answer 1

Pivot function always needs an Agregate fuction to work. So, as you cannot use SUM or AVG, you can use the MIN or MAX.

So, here´s a piece of code to get what you want.

SELECT PlanId, [Recreation], [Community party], [Employment]
FROM 
(SELECT PlanId, AreaDescription, GoalText
FROM dbo.TempArea) p
PIVOT
(
MAX (GoalText)
FOR AreaDescription IN
( [Recreation], [Community party], [Employment] )
) AS pvt
ORDER BY PlanId