How do I perform distinct grouping using MSSQL?

Asked Sep 08 '21 at 08:27

Active Sep 08 '21 at 08:42

Viewed 34 times

I have two tables named Animals and Details respectively:

ID  Name    Strength
1   Alice   20
2   Bob     30
3   Alex    45

ID  Zone
1   Z1
1   Z2
2   Z1
2   Z2
3   Z1
3   Z2
3   Z3

I want to form groups according to names like the one shown below(name and their distinct zones):

Name     Zone
Alice    Z1,Z2
Bob      Z1,Z2
Alex     Z1,Z2,Z3

For that I have written following MSSQL code but it is not giving correct output for Alex:

with cte as 
(
select a.name, d.zone from Details d join Animals a on a.ID=d.ID
)
select a1.name, concat(a1.zone+',', a2.zone) from cte a1, cte a2 where a1.name=a2.name and a1.zone<>a2.zone

What changes are required in this code? Please help!

asked Sep 08 '21 at 08:27

Anjali

Side note, it's 2021, *long* past time to have adopted the ANSI-92 JOIN syntax; it's been around for *29 years* now. There's no need to be using that old 1980's implicit JOIN syntax. – Thom A Sep 08 '21 at 08:31
@Larnu What is the alternative? – Anjali Sep 08 '21 at 08:33
Alternative to *what*? – Thom A Sep 08 '21 at 08:34
@Larnu I am able to get output for bob and alice using my code but not for Alex. thats my question – Anjali Sep 08 '21 at 08:35
1

See the linked duplicates, Anjali. – Thom A Sep 08 '21 at 08:35
1

I've added an [extra one](https://stackoverflow.com/a/273330/2029983) which I missed, which I actually think probably is the best of the above. – Thom A Sep 08 '21 at 08:42
1

[STUFF AND FOR XML PATH for String Concatenation](https://sqlwhisper.com/2013/03/24/stuff-and-for-xml-path-for-string-concatenation/) – huMpty duMpty Sep 08 '21 at 11:32

How do I perform distinct grouping using MSSQL?

0 Answers0