Comma separated output based on varchar field

Question

MS SQL Server 2008 Schema Setup:

CREATE TABLE AccessUser
(
    AccessUserID int, 
    AccessUserName varchar(255), 
    AccessUserType int, 
    AccessUserGroups varchar(255)
);

INSERT INTO AccessUser
(   AccessUserID, 
    AccessUserName, 
    AccessUserType, 
    AccessUserGroups
)
VALUES
    (1, 'Group 1', 2, ''),
    (2, 'Group 2', 2, ''),
    (3, 'John', 5, '@1@'),
    (4, 'Nick', 5, '@1@@2@');

Query 1:

SELECT DISTINCT
Empl.AccessUserName AS Name,
Firm.AccessUserName AS FirmName
FROM AccessUser AS Firm, AccessUser AS Empl
WHERE Empl.AccessUserType = 5
AND Empl.AccessUserGroups LIKE ('%@' + CAST(Firm.AccessUserID AS VARCHAR(10)) + '@%')
ORDER BY Empl.AccessUserName ASC

Results:

| NAME | FIRMNAME |
|------|----------|
| John |  Group 1 |
| Nick |  Group 1 |
| Nick |  Group 2 |

QUESTION: How can i modify the Query, to output this:

| NAME |      FIRMNAME      |
|------|--------------------|
| John |  Group 1           |
| Nick |  Group 1, Group 2  |

What I want to do is to commaseparate the Firmname, if the user has more than one AccessUserGroups. Is it even possible to do so in one query?

score 1 · Answer 1 · answered Jun 13 '14 at 10:11

You can do this way:

WITH CTE AS
(SELECT DISTINCT
Empl.AccessUserName AS Name,
Firm.AccessUserName AS FirmName
FROM AccessUser AS Firm, AccessUser AS Empl
WHERE Empl.AccessUserType = 5
AND Empl.AccessUserGroups LIKE ('%@' + CAST(Firm.AccessUserID AS VARCHAR(10)) + '@%'))

SELECT Name,
      FirmName = STUFF((
          SELECT ',' + convert(varchar(10),T2.FirmName)
          FROM CTE T2
          WHERE T1.Name = T2.Name
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM CTE T1
GROUP BY Name
ORDER BY FirmName

Result:

NAME    FIRMNAME
John    Group 1
Nick    Group 1,Group 2

Fiddle Demo

Comma separated output based on varchar field

1 Answers1