Pivoting data based on Group By using set-based solution

Question

Post reply

Pivoting data based on Group By using set-based solution

sqlenvy

Valued Member

Points: 59
More actions
April 6, 2010 at 11:41 am

#139334

Here's an example of the problem I'm trying to solve. I start with the following data in a table:
table code comment
t_a cd_1 com_1
t_a cd_2 com_2
t_a cd_3 com_3
t_b cd_1 com_1
t_b cd_2 com_2
t_b cd_3 com_3
t_c cd_1 com_1
t_c cd_2 com_2
t_c cd_3 com_3
Here's the format I need go get:
table code1 comment1 code2 comment2 code3 comment3
t_a cd_1 com_1 cd_2 com_2 cd_3 com_3
t_b cd_1 com_1 cd_2 com_2 cd_3 com_3
t_b cd_1 com_1 cd_2 com_2 cd_3 com_3
So what I'm trying to do is pivot all the data in the "code" and "comment" fields for each "table". I know I could create a temp table and then use a cursor but I want to find a set-based solution for this problem. I'm thinking I can somehow do this with a recursive cte but I just can't figure out how to do it. Thanks for any assistance you can provide.
Tim

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Which version of SQL are you using? You've posted this in the wrong forum and may not get the proper advice.

If you're using 2005 or 2008 look at the PIVOT function. It's what you're looking for.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

sqlenvy Valued Member Points: 59 More actions · Answer 2

oops, Thanks for the tip on that, Jason. I haven't posted before in SQL Server Central and really much anywhere else so I didn't realize I was in the wrong place.

I've used Pivot a little bit before but I just wasn't sure how to get the result I was looking for with this. I'll keep trying. Do yo know where I should be posting this question?

Thanks for your help,

Tim