JOIN 和 GROUP BY 與附加列

如果我在我的數據庫上執行以下查詢，我會得到下面粘貼的結果：

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

如何獲得按 ID 分組並具有不同位置列的相同結果？我想得到這樣的東西：

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

似乎您需要像這樣執行一些 CASE 語句以及一些 UNION ALL ：

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

如果您提供 SQLFiddle 或類似的東西，我們可以使用它。

您可以以行號為軸。不過，這對於大量列來說可能很乏味。

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

引用自：https://dba.stackexchange.com/questions/214305