2

I have done some searching and can see that this question has been asked several times before, but I can't seem to translate the solutions into my own working example. Can anyone point me in the right direction?

I have three database tables as below

People
+-------+------------+
|  uid  |   person   |
+-------+------------+
|   1   | Tom        |
+-------+------------+
|   2   | Dick       |
+-------+------------+
|   3   | Harry      |
+-------+------------+
|   4   | Peter      |
+-------+------------+
|   5   | Paul       |
+-------+------------+

Sports
+---------+----------------+
|   gid   |   group_name   |
+---------+----------------+
|    1    | Dancing        |
+---------+----------------+
|    2    | Golf           |
+---------+----------------+
|    3    | Football       |
+---------+----------------+
|    4    | Tennis         |
+---------+----------------+
|    5    | Squash         |
+---------+----------------+

Teams
+---------+---------+
|   gid   |   uid   |
+---------+---------+
|    1    |    1    |
+---------+---------+
|    2    |    1    |
+---------+---------+
|    3    |    1    |
+---------+---------+
|    1    |    2    |
+---------+---------+
|    2    |    2    |
+---------+---------+
|    3    |    2    |
+---------+---------+
|    4    |    2    |
+---------+---------+
|    2    |    3    |
+---------+---------+
|    1    |    4    |
+---------+---------+
|    5    |    4    |
+---------+---------+
|    1    |    5    |
+---------+---------+
|    4    |    5    |
+---------+---------+
|    3    |    5    |
+---------+---------+

I would like a SELECT query that returns one row per person listing all the sports they do (comma separated). In the example above, the results would be 

+-------+------------+----------------------------------+
|  uid  |   person   |   group_name                     |
+-------+------------+----------------------------------+
|   1   | Tom        | Dancing, Golf, Football          |
+-------+------------+----------------------------------+
|   2   | Dick       | Dancing, Golf, Football, Tennis  |
+-------+------------+----------------------------------+
|   3   | Harry      | Golf                             |
+-------+------------+----------------------------------+
|   4   | Peter      | Dancing, Squash                  |
+-------+------------+----------------------------------+
|   5   | Paul       | Tennis, Football                 |
+-------+------------+----------------------------------+

In MYSQL, I would use GROUP_CONCAT, but in this instance I am using SQL SERVER.

Can anyone help please?

Typhoon101
  • 2,063
  • 8
  • 32
  • 49
  • 2
    Try `STUFF... FOR XML PATH('')` – Ullas Mar 22 '16 at 13:08
  • Why didn't any of the solutions already posted help? This question shows no research effort. – Tab Alleman Mar 22 '16 at 13:11
  • Other solutions show how to gather information from two tables, whereas my needs are from three tables. My own efforts to modify it produce different errors, the latest being something like "Conversion failed when converting the varchar value ',' to data type int" – Typhoon101 Mar 22 '16 at 13:17
  • That's a casting error that probably has nothing to do with the group_concat simulation. It definitely isn't caused by the number of tables. Post your code so that we can debug it. – Tab Alleman Mar 22 '16 at 13:18
  • I have gone through many iterations of the code. The latest (and the only one I have left) only uses the first and last table. This fails before I even start to incorporate the third table. SELECT tbfc.uid, uids = STUFF((SELECT N', ' + uid FROM People AS tbg WHERE tbg.uid = tbfc.uid ORDER BY tbfc.uid FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') FROM Teams AS tbfc – Typhoon101 Mar 22 '16 at 13:44

1 Answers1

3
SELECT p.uid, p.person,
       stuff (g.group_name, 1, 1, '') as group_name
FROM   People p
CROSS APPLY
(
    SELECT ',' + s.group_name
    FROM   Teams t
           INNER JOIN Sports s ON t.gid = s.gid
    WHERE  t.uid = p.uid
    ORDER BY s.group_name
    FOR XML PATH ('')
) g (group_name)
Squirrel
  • 23,507
  • 4
  • 34
  • 32