CODEWITHSUNDEEP
sql-server
Frenz
Frenz

Reputation: 747

SQL query to get list of customers with group name

I have the following tables

Customer Table

Groups Table

Customer_Group Table

A customer may or may not belong to a group. If they belong to any/multiple groups, then an entry is made in the Customer_Group Table. I need an SQL query that will list all the customers along with their group name. If they don't belong to any group then it can show NULL. Otherwise, the group name should be shown as comma separated. For example as below

CustomerId   CustomerName   GroupName
1            ABC            NULL 
2            DEF            Sales,Marketing

The Customer Table data would be as follows

CustomerId  CustomerName   
1           ABC
2           DEF

The Group Table data would be as follows

GroupId   GroupName
1         Sales
2         Marketing

The Customer_Group data would be as follows

CustomerId GroupId   
2          1
2          2

Please help me with SQL query that will list all the customers along with their group name. I'm using Microsoft SQL Server 2005. Thanks in advance.

Upvotes: 3

Views: 4943

Answers (1)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

May be Something like this

SELECT C.CustomerId, C.CustomerName,
       GroupName = STUFF(
         (SELECT ',' + GI.GroupName FROM [Group] AS GI           
           INNER JOIN Customer_Group AS CGI ON CGI.GroupId = GI.GroupId
           WHERE CGI.CustomerId = C.CustomerId
           FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
FROM Customer C LEFT JOIN Customer_Group CG ON C.CustomerId = CG.CustomerId
GROUP BY C.CustomerId, C.CustomerName

FIDDLE DEMO

Output would be

CustomerId   CustomerName   GroupName
+------------------------------------+
1            ABC            NULL 
2            DEF            Sales,Marketing

Upvotes: 4

Related Questions