question

Papil avatar image
0 Votes"
Papil asked MelissaMa-msft commented

Group by data with matching order of columns

86417-test.jpg



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.

sql-server-generalsql-server-transact-sql
test.jpg (35.6 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It would be much easier to help you if we had more details. What do you mean by "trying to combine the two rows" - combine them how? Are you doing this in a query, or in a graphical tool, and what is the query, or what tool and what parameters?

Also, your illustration does not seem to match your question. The combined values in the second table are in the same order they were in in the table above. What, exactly, are you trying to do, and how is this showing it's not working?

0 Votes 0 ·

here is what i have been trying. issue is with the order to match Test corresponds to Test1 and should show up as Test 1 test3 ( in that same order).
SELECT
STUFF(
(
SELECT distinct char(13) + T2.Issue
FROM #temp T2
WHERE T.emp1 = T2.emp1
FOR XML PATH('')
, TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') + CHAR(13) AS issue,
STUFF(
(
SELECT distinct char(13) + T2.Type
FROM #temp T2
WHERE T.emp1 = T2.emp1
FOR XML PATH('')
, TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') + CHAR(13) AS Type,
emp1
,name1
FROM #temp T
WHERE
Emp1=1 AND
GROUP BY
emp1
,name1

0 Votes 0 ·

Hi @Papil,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
Viorel-1 avatar image
1 Vote"
Viorel-1 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @Papil,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.