0

I have a very simple data set that I would like to be able to query and get the results as a single record.

Members Table
ID        | FirstName | LastName | HeroName
42        |  Bruce    | Wayne    | Batman
1337      |  Bruce    | Banner   | Hulk
1033      |  Clark    | Kent     | Newspaper Boy

Skills Tables
ID        | Skill
42        | Martial Arts
42        | Engineering
42        | Intimidation
1337      | Anger Management
1337      | Thermo Nuclear Dynamics
1033      | NULL

I want the result to be 

ID | FirstName | LastName | HeroName | Skill1       | Skill2      | Skill3       | ... | Skilln
42   Bruce     | Wayne    | Batman   | Martial Arts | Engineering | Intimidation

The query I have so far is

SELECT m.ID, m.FirstName, m.LastName, m.HeroName, s.Skill
FROM Members m
JOIN Skills s
ON m.ID = s.ID
WHERE m.ID = 42 and s.Skill IS NOT NULL

which returns 

ID | FirstName | LastName | HeroName | Skill
42 | Bruce     | Wayne    | Batman   | Martial Arts
42 | Bruce     | Wayne    | Batman   | Engineering
42 | Bruce     | Wayne    | Batman   | Intimidation

Short of iterating over the results and only extracting the fields I want is there a way to return this as a single record? I've seen topics on PIVOT, and XmlPath but from what I've read neither of these does quite what I want it to. I'd like an arbitrary number of Skills to be returned and no nulls are returned.

EDIT:

The problem with PIVOT is that it will turn one of the rows into a column header. If There is a way to fill in a generic column header than it might work.

TheDude
  • 1,239
  • 1
  • 21
  • 47
  • possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) – jpw Jul 30 '15 at 00:09
  • The first answer in the duplicate question shows exactly how to do what you want with both a fixed and unknown number of columns. – jpw Jul 30 '15 at 00:10
  • I'll try it out an see if that resolves it. I'm guessing I need to pivot after my join right? – TheDude Jul 30 '15 at 00:38

0 Answers0