Group by data with matching order of columns

Question

Hi,
I am trying to combine the two rows into 1 but order of Type and issue matters. when i group the data and do stuff on column type and issue. I dont get correct order as shown in the second table in screenshot. if Test shows up before then Test1 should show up before in the combined row. Please suggest. Thanks.

Answer 1

It is problematic to keep an order if there is no column that offers an ordering, such as a primary key. Try the next workaround:

;
with Q as
(
    select *, 
        row_number() over (order by Name1) as rn
    from #temp
)
SELECT
    STUFF(
        (
            SELECT char(13) + Issue
            FROM Q
            WHERE emp1 = T.emp1
            group by Issue
            order by min(rn)
            FOR XML PATH(''), TYPE
        ).value('.[1]', 'nvarchar(max)'), 
        1, 1, ''
    ) + CHAR(13) AS issue,
    STUFF(
        (
            SELECT char(13) + Type
            FROM Q
            WHERE emp1 = T.emp1
            group by Type
            order by min(rn)
            FOR XML PATH(''), TYPE
        ).value('.[1]', 'nvarchar(max)'), 
        1, 1, ''
    ) + CHAR(13) AS Type,
    emp1, 
    name1
FROM Q T
WHERE Emp1 = 1
GROUP BY emp1, name1

If it does not work, then show sample data to reproduce the problem.

Answer 2

Hi @Papil11 ,

Welcome to Microsoft Q&A!

Please refer below which has a small modification on Viorel's query:

;with cte as   
(  
 select *, row_number() over (partition by Emp1,Name1 order by Type) as rn  
     from #temp  
)  
SELECT   
STUFF(  
(  
SELECT  char(13) + T2.Issue  
FROM cte T2  
WHERE T.emp1 = T2.emp1  
order by rn  
FOR XML PATH('')  
, TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') + CHAR(13) AS issue,  
STUFF(  
(  
SELECT  char(13) + T2.Type  
FROM cte T2  
WHERE T.emp1 = T2.emp1  
order by rn  
FOR XML PATH('')  
, TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') + CHAR(13) AS Type,  
emp1  
,name1  
FROM cte T  
WHERE  
Emp1=1   
GROUP BY emp1, name1  

If you have SQL Server 2017 and later, you could also use STRING_AGG as below:

;with cte as   
(  
 select *, row_number() over (partition by Emp1,Name1 order by Type) as rn  
     from #temp  
)  
SELECT STRING_AGG([Type], CHAR(13))  AS [Type]   
,  STRING_AGG([issue], CHAR(13))  AS [issue]  
,emp1, name1  
FROM cte   
group by emp1, name1  

OR

SELECT STRING_AGG([Type], CHAR(13)) WITHIN GROUP (ORDER BY [Type] ASC) AS [Type]   
,  STRING_AGG([issue], CHAR(13)) WITHIN GROUP (ORDER BY [Type] ASC)  AS [issue]  
,emp1, name1  
FROM #temp   
group by emp1, name1  

Output:

Type	issue	emp1	name1  
Test Test2	Test1 Test3	1	Emp1  

If above are not working, please provide more sample data and expected output.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.