0

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
3
  • Specify your DBMS.
    – Akina
    Aug 7, 2018 at 16:10
  • Is any criteria how items with the same TEXT should be ordered?
    – Kondybas
    Aug 7, 2018 at 16:13
  • DBMS: MSSQL Server There is no need to order TEXT, ordering by ID would be enough.
    – user110366
    Aug 8, 2018 at 9:03

2 Answers 2

0

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.

1
  • If the OP provided a SQL Fiddle and you tried your query, you would discover that SQL Server doesn't support the LIMIT syntax.
    – Andriy M
    Aug 13, 2018 at 9:33
0

You can pivot on a rownumber. This can be tedious for large numbers of columns though.

SELECT ID, [Text], [1] AS Location1, [2] AS Location1, [3] AS Location3
FROM (
    SELECT t.id as ID,
        j.text as Text,
        l.location as Location,
        rn = ROW_NUMBER() OVER (PARTITION BY ID, [Text] ORDER BY (SELECT 1))
    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
) l
PIVOT (
   MIN(l.Location)
   FOR rn IN ([1],[2],[3])
) pvt

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.