JOIN and GROUP BY with additional columns

join;queryselect

If I run the following query on my database I get the results pasted below:

SELECT t.id as ID,
j.text as Text,
l.location as Location, 
FROM [text] t WITH (nolock)
INNER JOIN Text_Location tl WITH (nolock) on tl.textid = t.id
INNER JOIN Location l WITH (nolock) on tl.locationId = l.id


    ID       Text      Location     
---------- ---------- ---------- 
     1         FC      London
     1         FC       Paris
     2         KG      Berlin
     3        TX1        Roma
     3        TX1      Vienna
     3        TX1      Dublin

How could I get the same results grouped by ID and having different columns for location? I would like to get something like this:

    ID       TEXT    LOCATION1   LOCATION2  LOCATION3 
---------- ---------- ---------- ---------- ----------
     1         FC     London      Paris        
     2         KG     Berlin            
     3        TX1     Roma        Vienna     Dublin

Best Answer

Seems you would need to do some CASE statements along with some UNION ALL like this:

SELECT id, 
       CASE 
              WHEN idcounter <=1 THEN text1 
              ELSE "" 
       end AS text1, 
       CASE 
              WHEN idcounter <=1 THEN location1 
              ELSE "" 
       end AS location1, 
       CASE 
              WHEN idcounter <=2 THEN text2 
              ELSE "" 
       end AS text2, 
       CASE 
              WHEN idcounter <=2 THEN location2 
              ELSE "" 
       end AS location2, 
       CASE 
              WHEN idcounter <=3 THEN text3 
              ELSE "" 
       end AS text3, 
       CASE 
              WHEN idcounter <=3 THEN location3 
              ELSE "" 
       end AS location3 
FROM  ( 
                SELECT   t.id        AS id, 
                         Count(t.id) AS idcounter 
                group BY t.id ) AS results1 
UNION ALL 
SELECT     t.id       AS id, 
           j.text     AS text1, 
           l.location AS location1, 
FROM       [text] t WITH (nolock) 
INNER JOIN text_location tl WITH (nolock) 
ON         tl.textid = t.id 
INNER JOIN location l WITH (nolock) 
ON         tl.locationid = l.id 
ORDER BY   l.location 
LIMIT      1 
UNION ALL 
SELECT     t.id       AS id, 
           j.text     AS text2, 
           l.location AS location3, 
FROM       [text] t WITH (nolock) 
INNER JOIN text_location tl WITH (nolock) 
ON         tl.textid = t.id 
INNER JOIN location l WITH (nolock) 
ON         tl.locationid = l.id 
ORDER BY   l.location 
LIMIT      1 
offset     1 
UNION ALL 
SELECT     t.id       AS id, 
           j.text     AS text3, 
           l.location AS location3, 
FROM       [text] t WITH (nolock) 
INNER JOIN text_location tl WITH (nolock) 
ON         tl.textid = t.id 
INNER JOIN location l WITH (nolock) 
ON         tl.locationid = l.id 
ORDER BY   l.location 
LIMIT      1 
offset     2

If you provide an SQLFiddle or some such we could play with it.

Related Question