How to concatenate the values in the order of occurrence?-CodePudding

Below is my sample data

Here is the output i want using an sql query. The concatenated string should hold unique values in the order of their occurrence. Please help.

CodePudding user response：

this should do it

select sub.issue, STRING_AGG(sub.type, ', ')  within group (order by sub.orderField) [Types]
, count(sub.[type]) TypeCount
from (
    select issue, MIN(c.time) orderField, c.[type] ,count(c.[type]) amt
    from concentrate c
    group by c.issue, c.[type]) sub
group by sub.issue

CodePudding user response：

This will solve this problem, used STUFF inbuilt method with XML to solve it and it worked for me. I have helped create sample table, all you need to do is execute this in MSSQL sever.

     DECLARE @TEMP TABLE(ISSUE VARCHAR(50), [TYPE] VARCHAR(10))

     INSERT INTO @TEMP
     VALUES ('A', 'Apple'),
            ('A', 'Apple'),
            ('A', 'Apple'),
            ('A', 'Orange'),
            ('A', 'Banana')

     ;with cte as (
         select ISSUE , STUFF((SELECT DISTINCT ','   [TYPE] from @TEMP T WHERE T.ISSUE = TP.ISSUE FOR XML PATH('')), 1,1,'')  [TYPE], 
         COUNT(DISTINCT [TYPE]) AS Total from @TEMP TP
         GROUP BY [TYPE], ISSUE
     )
     SELECT ISSUE, [TYPE], SUM(Total)  Total FROM CTE
     GROUP BY ISSUE, [TYPE]