1

I need to perform a row concatenation Operation in SQL Server, for those rows which all have the same Master_ID. Also, the resulted output order is based on the Seq_No Column.

As I am using an older version of SQL Server, I am unable to use STRING_AGG() function.

As of now, I am using Stuff and XML PATH functions to achieve the row concatenation, but I am unable to order the resulted data based on the Seq_No Column.

Table script:

DECLARE @T TABLE (Master_ID INT,
Associated_ID INT,
Class_ID INT,
Code VARCHAR(20),SEQ_No INT)


Insert into @T VALUES(1297232,NULL,3619202, '1101'  ,1)
Insert into @T VALUES(1297232,NULL,3619202, '0813'  ,2)
Insert into @T VALUES(1297232,NULL,3619202, '170219'    ,3)
Insert into @T VALUES(1297232,NULL,3619202, '19053299',1)
Insert into @T VALUES(1297232,1297233,3619202,'1101'    ,1)
Insert into @T VALUES(1297232,1297233,3619202,'0813'    ,2)
Insert into @T VALUES(1297232,1297233,3619202,'170219'  ,3)
Insert into @T VALUES(1297232,1297233,3619202,'19053299'    ,1)
Insert into @T VALUES(1297232,1297234,3619202,'1101'    ,1)
Insert into @T VALUES(1297232,1297234,3619202,'0813'    ,2)
Insert into @T VALUES(1297232,1297234,3619202,'170219'  ,3)
Insert into @T VALUES(1297232,1297234,3619202,'19053299'    ,1)
Insert into @T VALUES(1297232,1297235,3619202,'1101'    ,1)
Insert into @T VALUES(1297232,1297235,3619202,'0813'    ,2)
Insert into @T VALUES(1297232,1297235,3619202,'170219'  ,3)
Insert into @T VALUES(1297232,1297235,3619202,'19053299'    ,1)

SELECT * FROM @T

The query I tried with error:

SELECT STUFF((SELECT DISTINCT' ,'+Code
FROM @T
ORDER by ISNULL(Associated_ID,Master_ID),SEQ_No -- Reason for Error
FOR XML PATH (''),TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

Output for the above code:

0813 ,1101 ,170219 ,19053299

Expected output:

1101,19053299,0813,170219
James Z
  • 12,209
  • 10
  • 24
  • 44
Thiyagu
  • 1,260
  • 1
  • 5
  • 14

1 Answers1

1

You can swap DISTINCT for GROUP BY as they to the same and then you can order by aggregation functions like SUM or MAX

Example:

SELECT STUFF((SELECT ' ,'+Code
FROM @T
GROUP BY Code
ORDER by SUM(ISNULL(Associated_ID,Master_ID)),SUM(SEQ_No)
FOR XML PATH (''),TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 

-- OUTPUT: 1101 ,19053299 ,0813 ,170219

The error

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

has nothing to do with the stuff, but the fact you are trying to sort on a distinct. More info

Dale K
  • 25,246
  • 15
  • 42
  • 71
Preben Huybrechts
  • 5,853
  • 2
  • 27
  • 63