consolidating multiple rows to single row in a view

Question

Post reply

consolidating multiple rows to single row in a view

david-731819

SSC Rookie

Points: 34
More actions
June 17, 2008 at 5:16 pm

#381200

This is probably very simple, but I am still learning and haven't been able to figure this out.
I have a table that lists employee skills:
employee1 skill1
employee1 skill2
employee1 skill3
employee2 skill2
employee3 skill1
employee3 skill5
I need to create a view that shows all the skills for each employee on the same row, either concatenated:
employee1 skill1, skill2, skill3
employee2 skill2
employee3 skill1, skill5
or in seperate columns
employee1 skill1 skill2 skill3
employee2 skill2 null null
employee3 skill1 skill5 null
One of the challenges is that an employee may have anywhere from 0 to ten skills.
Anyone have an easy solution?

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

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

david (6/17/2008)
Anyone have an easy solution?

Yes I do... please see the following... tells you not only how to do it, but how to do it right... 😀

http://www.sqlservercentral.com/articles/Test+Data/61572/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

david-731819 SSC Rookie Points: 34 More actions · Answer 2

Thank you Jeff,

This looks like it will do the trick.

I will be working on this tomorrow.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

You bet... thanks for the feedback... let us know if you have problems with the implementation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

david-731819 SSC Rookie Points: 34 More actions · Answer 4

Got my project done! While the code that you provided is over my current comprehensive level, I was able to incorperate it into my query and it works beautifully. It always amazes me how there is such a huge range of efficiency in code.... always seems like the most efficient are the hardest to comprehend. Thanks a million,

David

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Very cool... thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)