SQL Server put the same data of category together

Question

I use SQL to simulate data with similar categories:

select TA.leaderCD, TA.EmpCD, TA.message
from  
    (select '1001' as EmpCD, 'L001' as leaderCD,'M1' as message
     union 
     select '1001','L002','M1'
     union 
     select '1001', 'L003','M1'
     union 
     select '1002', 'L001','M1'
     union 
     select '1002', 'L002','M2'
     union 
     select '1002', 'L004','M2'
     union 
     select '1003', 'L002','M1'
     union 
     select '1003', 'L003','M1'
     union 
     select '1003', 'L005','M2'
     union 
     select '1004', 'L002','M1'
     union 
     select '1004', 'L003','M2'
     union 
     select '1004', 'L004','M2'
     union 
     select '1004', 'L005','M3') as TA
order by 
    TA.leaderCD

The result is:

leaderCD EmpCD  message
L001     1001   M1
L001     1002   M1
L002     1001   M1
L002     1002   M2
L002     1003   M1
L002     1004   M1
L003     1001   M1
L003     1003   M1
L003     1004   M2
L004     1002   M2
L004     1004   M2
L005     1003   M2
L005     1004   M3

I want to separate the data according to the 1st column and the 3rd column.

If the 1st column(leaderCD) and the 3rd column(message) have the same data, merge the 2nd column together,use semicolon,just like this:

leaderCD EmpALL         message
L001     1001;1002      M1
L002     1001;1003;1004 M1
L002     1002           M2
L003     1001;1003      M1
L003     1004           M2
L004     1002;1004      M2
L005     1003           M2
L005     1004           M3

I tried group by,but I find group by can't do this.

What should I do?

I like you actually provide a query which can create data to a table variable which let people able to answer your question way sooner. — Circle Hsiao, May 16 '19 at 02:57
I think people who downvoted might just feel the resultset you are seeking isn't considered as a good structure. — Circle Hsiao, May 16 '19 at 03:00

score 1 · Accepted Answer · edited May 16 '19 at 03:00

I'm not sure if you did enough research for this question, but I have included a potential solution for you. Here is the SQLFiddle. The code is below:

SELECT EE.LeaderCD AS [Leader], EE.Message AS [Message], 
STUFF((SELECT ', ' + EmpCD 
       FROM Employees e 
       WHERE e.LeaderCD = ee.LeaderCD AND e.Message = ee.Message
       FOR XML PATH('')), 1, 1, '') AS [Employees]
FROM Employees ee
GROUP BY ee.LeaderCD, ee.Message

The way this code works is, hopefully, straightforward. STUFF is a SQL function that, basically, allows you to replace text in a string with a specified start and stop. This is creating a comma separated list (you can see more on this here) and then joining it back to the main table when the employee ID and message match. We could utilize a select distinct here to prevent duplication, but I don't believe that's going to be necessary due to the way we're building the string. For XML Path is a different bit of code that basically creates an XML file based on your query results, and in this case the way we're using it there is no path being created (hence blank string) -- you can read more about that here. I hope this helps, please let me know if you have any other questions.

You're welcome, Rhaegal. Ganesh's solution also works if you're on SQL server 2017 or newer, although I do wish he had added a little more explanation - I don't have the rep to comment on his post! I found http://www.sqlservertutorial.net/sql-server-string-functions/sql-server-string_agg-function/ to be a little helpful - I actually wasn't aware of the STRING_AGG function. — Sean Brookins, May 16 '19 at 02:33
You deserve the privilege to comment everywhere, I always feel 50 points to comment is a bit too high. — Circle Hsiao, May 16 '19 at 02:45
Well @蕭為元 I think you just helped me pass the 50 point mark :). I'm glad I could help contribute on this one. — Sean Brookins, May 16 '19 at 02:55

score 1 · Answer 2 · edited May 16 '19 at 02:59

1

If you are using SQL Server 2017 try this:

SELECT
    leadercd
    ,message
    ,STRING_AGG(empcd, ', ') AS e
FROM
    t
GROUP BY
    leadercd,message;

http://sqlfiddle.com/#!18/56434/17

edited May 16 '19 at 02:59

Arulkumar

12,966
14
47
68

answered May 16 '19 at 02:30

Ganesh Chandrasekaran

1,578
12
17

Sorry,sir,I use sqlserver2008. – May 16 '19 at 02:32
Please don't add link only answers, if the SQLFiddle site is down, the feature users can't access your query. – Arulkumar May 16 '19 at 02:59

SQL Server put the same data of category together

2 Answers2