What the simplest way to sub-query a variable number of rows into fields of the parent query?

Question

What the simplest way to sub-query a variable number of rows into fields of the parent query?

PeopleTBL
   NameID int - unique
   Name   varchar

Data: 1,joe
      2,frank
      3,sam

---

HobbyTBL
   HobbyID   int - unique
   HobbyName varchar

Data: 1,skiing
      2,swimming

---

HobbiesTBL
    NameID  int
    HobbyID int

Data: 1,1
      2,1
      2,2   

---

The app defines 0-2 Hobbies per NameID.

What the simplest way to query the Hobbies into fields retrieved with "Select * from PeopleTBL"

Result desired based on above data:

NameID  Name    Hobby1  Hobby2  
1        joe     skiing
2        frank   skiing  swimming
3        sam

Answer 1

I'm not sure if I understand correctly, but if you want to fetch all the hobbies for a person in one row, the following query might be useful (MySQL):

SELECT NameID, Name, GROUP_CONCAT(HobbyName) AS Hobbies
FROM PeopleTBL
JOIN HobbiesTBL USING NameID
JOIN HobbyTBL USING HobbyID

Hobbies column will contain all hobbies of a person separated by ,. See documentation for GROUP_CONCAT for details.

I don't know what engine are you using, so I've provided an example with MySQL (I don't know what other sql engines support this).

Answer 2

Select P.NameId, P.Name
    , Min( Case When H2.HobbyId = 1 Then H.HobbyName End ) As Hobby1
    , Min( Case When H2.HobbyId = 2 Then H.HobbyName End ) As Hobby2
From HobbyTbl As H
    Join HobbiesTbl As H2
        On H2.HobbyId = H.HobbyId
    Join PeopleTbl As P
        On P.NameId = H2.NameId
Group By P.NameId, P.Name

What you are seeking is called a crosstab query. As long as the columns are static, you can use the above solution. However, if you want to dynamic build the columns, you need to build the SQL statement in middle-tier code or use a reporting tool.