TSQL How to select element by index in a grouped query-CodePudding

I have this table:

MeasureId | Tag
----------------------
1         | CODE-ABC
1         | CODE-EFG
1         | CODE-HGT
2         | CODE-XXX
2         | CODE-YYY

given that each Measure has a maximum of 3 Tags what I need is

MeasureId | Tag1     | Tag2     | Tag3
--------------------------------------------
1         | CODE-ABC | CODE-EFG | CODE-HGT
2         | CODE-XXX | CODE-YYY | <null>

i.e. I should group by MeasureId but I don't know how to access a single element using an index in the select part, something like this:

SELECT MeasureId, Tag[0], Tag[1], Tag[2] FROM Measures GROUP BY MeasureId

So far I just came up with something like this:

SELECT MeasureId, STRING_ADD(Tag, ',') as Tag FROM Measures GROUP BY MeasureId

resulting in this:

MeasureId | Tag 
--------------------------------------------
1         | CODE-ABC,CODE-EFG,CODE-HGT
2         | CODE-XXX,CODE-YYY

Is something doable in TSQL (Azure Sql)?

CodePudding user response：

WITH TABLE_DATA(MeasureId,Tag) AS
(
  SELECT 1  ,        'CODE-ABC' UNION ALL
  SELECT 1  ,        'CODE-EFG'UNION ALL
  SELECT 1  ,        'CODE-HGT'UNION ALL
  SELECT 2  ,        'CODE-XXX'UNION ALL
  SELECT 2  ,        'CODE-YYY'
)
SELECT X.MeasureId,
MAX(
     CASE
      WHEN X.XCOL=1 THEN X.TAG 
      ELSE ''
    END
   ) AS  TAG_1,
 MAX(
     CASE
      WHEN X.XCOL=2 THEN X.TAG 
      ELSE ''
     END
    ) AS  TAG_2,
   MAX(
       CASE
        WHEN X.XCOL=3 THEN X.TAG 
        ELSE ''
       END
     ) AS  TAG_3
FROM
(
   SELECT TD.MeasureId,TD.Tag,
    ROW_NUMBER()OVER(PARTITION BY TD.MeasureId ORDER BY TD.Tag)XCOL
    FROM TABLE_DATA AS TD
)AS X  
GROUP BY X.MeasureId

CodePudding user response：

A combination of PIVOT and ROW_NUMBER() is an option:

SELECT *
FROM (  
   SELECT MeasureId, Tag, CONCAT('Tag', ROW_NUMBER() OVER (PARTITION BY MeasureId ORDER BY Tag)) AS TagId
   FROM Measures
) t
PIVOT (
   MAX(Tag) FOR TagId IN ([Tag1], [Tag2], [Tag3])
) p