Need help with basic pivoting in 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with basic pivoting in 2005

Author

Topic

bosstone75
Starting Member

11 Posts

Posted - 2010-04-29 : 09:17:26

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...

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.

Any ideas?

Thanks! Bill

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-29 : 09:32:55

What is GoalText data type?

bosstone75
Starting Member

11 Posts

Posted - 2010-04-29 : 09:52:44

Thanks ms65g,

It's (text, null)

PlanID is (FK, int, null)
AreaAbbrev is (varchar(10), null)
AreaDesc is (varchar(50), null)

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-29 : 10:02:21

Try this:

;WITH Rec AS
(SELECT PlanID, GoalText
   FROM table_name
  WHERE AreaAbbrev = 'Rec'),
  Community AS
  (SELECT PlanID, GoalText
   FROM table_name
  WHERE AreaAbbrev = 'Community'),
    Employment AS
  (SELECT PlanID, GoalText
   FROM table_name
  WHERE AreaAbbrev = 'Employment')
  
SELECT D.PlanID, R.GoalText, C.GoalText, E.GoalText
FROM (SELECT DISTINCT PlanID FROM table_name)D
LEFT OUTER JOIN Rec R ON D.PlanID = D.PlanID
LEFT OUTER JOIN Community C ON D.PlanID = C.PlanID
LEFT OUTER JOIN Employment E ON D.PlanID = E.PlanID;

bosstone75
Starting Member

11 Posts

Posted - 2010-04-29 : 10:10:58

Ok... I'm sorry... I'm really new to this. Here's the exact script I ran. The next post will be the error messages I got.

;WITH Rec AS
(SELECT PlanID, GoalText
FROM [dbo].[plantransitionareas]
WHERE AreaAbbrev = 'Rec'),
Community AS
(SELECT PlanID, GoalText
FROM [dbo].[plantransitionareas]
WHERE AreaAbbrev = 'Community'),
Employment AS
(SELECT PlanID, GoalText
FROM [dbo].[plantransitionareas]
WHERE AreaAbbrev = 'Employment')

SELECT D.PlanID, R.GoalText, C.GoalText, E.GoalText
FROM (SELECT DISTINCT PlanID FROM [dbo].[plantransitionareas])D
LEFT OUTER JOIN Rec R ON D.PlanID = D.PlanID
LEFT OUTER JOIN Community C ON D.PlanID = C.PlanID
LEFT OUTER JOIN Employment E ON D.PlanID = E.PlanID;

bosstone75
Starting Member

11 Posts

Posted - 2010-04-29 : 10:14:32

Wait... I had some notes at the top that screwed up the line numbers for troubleshooting. Here's the errors with those notes removed so it's easier to identify where the problems are happening.

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.
Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ','.
Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near ','.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-29 : 10:23:48

Try this:



SELECT D.PlanID
	, R.GoalText AS Rec
	, C.GoalText AS Community
	, E.GoalText AS Employment
FROM (SELECT DISTINCT PlanID FROM [dbo].[plantransitionareas])D
LEFT OUTER JOIN (SELECT PlanID, GoalText
		 FROM [dbo].[plantransitionareas]
		 WHERE AreaAbbrev = 'Rec')R ON D.PlanID = D.PlanID
LEFT OUTER JOIN (SELECT PlanID, GoalText
		FROM [dbo].[plantransitionareas]
		WHERE AreaAbbrev = 'Community')C ON D.PlanID = C.PlanID
LEFT OUTER JOIN (SELECT PlanID, GoalText
		FROM [dbo].[plantransitionareas]
		WHERE AreaAbbrev = 'Employment')E ON D.PlanID = E.PlanID;

bosstone75
Starting Member

11 Posts

Posted - 2010-04-29 : 11:00:34

THANKS! That seems to have done it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources