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.

HopAlongPolly
  • 1,347
  • 1
  • 20
  • 48
  • 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? – HopAlongPolly Jul 30 '15 at 00:38

1 Answers1

0
select s.id,s.name,sk.skills as skill1,sk1.skills as skills2,sk2.skills as skills3,sk3.skills as skills4 
                   from students_57 s 
    left join skills sk
    on s.id=sk.id and sk.skills='sql server'
    left join skills sk1
    on s.id=sk1.id and sk1.skills='Python'
    left join skills sk2
    on s.id=sk2.id and sk2.skills='adf'
    left join skills sk3
    on s.id=sk3.id and sk3.skills='databricks'
4b0
  • 21,981
  • 30
  • 95
  • 142
Sankruth
  • 1
  • 2
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 26 '23 at 11:11