SQL concatenating in a select horizontally

Question

Can you help me please? I'm using the SQL indicated below:

SELECT 
    DISTINCT C.Field1 As 'Group',
    A.Field2 As 'Security Object'
    E.Field3 As 'User'
FROM TableA AS A
JOIN TableB AS B ON B.key1 = A.Key1
JOIN TableC AS C ON C.Key1 = B.key2
JOIN TableD AS D ON D.key1 = C.key2
where A.Field1 = 'ObjectA'
GROUP BY C.Field1, A.Field2, E.Field3

Typical Output

Group   Security Object User
Group1  ObjectA UserA
Group1  ObjectA UserB
Group1  ObjectA UserC
Group2  ObjectA UserE
Group2  ObjectA UserF
Group2  ObjectA UserH
etc...

I want to output something more like:

Group   Security Object User
Group1  ObjectA UserA, UserB, UserC
Group2  ObjectA UserE, UserF, UserH
etc

However I'm having difficulties concatenating the user field in the horizontal form indicated. Can you help me please? I know there is lots about this online, but nothing is working properly :-( J.

Answer 1

This is a solution without using something like pivot. From all user names I simply build a comma list. The example does not use your SQL but the result set and delivers the wanted output:

Group1  ObjectA   UserA, UserB, UserC
Group2  ObjectA   UserE, UserF, UserH

I renamed some of your column names to avoid keyword conflicts. The interesting part within this SQL is the useage of for xmlpath, which is IMHO the way to provide comma lists using SQLServer.

select 
distinct 
group2, security,
substring((select ', ' + user2 from table1 t 
    where t.group2=table1.group2 and t.security=table1.security 
    order by t.user2
    for xml path('')), 3, 10000) as User_List
from table1

http://sqlfiddle.com/#!6/61b9b6/4

If there is another better way, maybe new within SQLServer 2014, I am eager to listen.

Answer 2

I have setup the example below to demonstrate

1. Use of an APPLY operator with FOR XML PATH. I have used an OUTER APPLY as there is a User with no access to objects.
2. In the first query below I avoid "XML entities" in the result. Note the object names contain characters that XML needs to form XML. The second query below then displays what happens if TYPE and .value(... are omitted.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Users  
    ([Code] varchar(1), [UserName] varchar(4))
;

INSERT INTO Users   
    ([Code], [UserName])
VALUES
    ('A', 'Aaaa'),
    ('B', 'Bbbb'),
    ('C', 'Cccc'),
    ('D', 'Dddd'),
    ('E', 'Eeee')
;


CREATE TABLE Objects    
    ([Code] varchar(7), [Name] varchar(23))
;

INSERT INTO Objects 
    ([Code], [Name])
VALUES
    ('ObjectA', 'Fred & Ginger Whizzbang'),
    ('ObjectB', 'The diamond<> cutter'),
    ('ObjectC', 'Whatever'),
    ('ObjectD', 'Blah de Blah')
;

CREATE TABLE ObjectAccess
    ([ID] int, [ObjectCode] varchar(7), [UserCode] varchar(1))
;

INSERT INTO ObjectAccess
    ([ID], [ObjectCode], [UserCode])
VALUES
    (1, 'ObjectA', 'A'),
    (2, 'ObjectA', 'B'),
    (3, 'ObjectA', 'C'),
    (4, 'ObjectB', 'D'),
    (5, 'ObjectB', 'A'),
    (6, 'ObjectB', 'B'),
    (7, 'ObjectB', 'C'),
    (8, 'ObjectC', 'B'),
    (9, 'ObjectC', 'C'),
    (10, 'ObjectC', 'D'),
    (11, 'ObjectD', 'C'),
    (12, 'ObjectD', 'D'),
    (13, 'ObjectE', 'A'),
    (14, 'ObjectF', 'D')
;

Query 1:

SELECT
      Users.code
    , Users.UserName
    , oa.ObjectList
FROM Users
      OUTER APPLY (
            SELECT
                  STUFF
                  ((
                        SELECT /* DISTINCT -- can be used here if required */
                              ',' + Objects.Name
                        FROM ObjectAccess
                              INNER JOIN Objects ON ObjectAccess.ObjectCode = Objects.Code
                        WHERE ObjectAccess.UserCode = Users.Code
                        ORDER BY Objects.Name
                        FOR XML PATH (''), TYPE --<< nb!!
                  )
                  .value('.', 'varchar(max)') --<< nb!!
                  , 1, 1, '')
      ) AS oa (ObjectList)

Results:

| code | UserName |                                                         ObjectList |
|------|----------|--------------------------------------------------------------------|
|    A |     Aaaa |                       Fred & Ginger Whizzbang,The diamond<> cutter |
|    B |     Bbbb |              Fred & Ginger Whizzbang,The diamond<> cutter,Whatever |
|    C |     Cccc | Blah de Blah,Fred & Ginger Whizzbang,The diamond<> cutter,Whatever |
|    D |     Dddd |                         Blah de Blah,The diamond<> cutter,Whatever |
|    E |     Eeee |                                                             (null) |

Query 2:

SELECT
      Users.code
    , Users.UserName
    , oa.ObjectList
FROM Users
      OUTER APPLY (
            SELECT
                  STUFF
                  ((
                        SELECT /* DISTINCT -- can be used here if required */
                              ',' + Objects.Name
                        FROM ObjectAccess
                              INNER JOIN Objects ON ObjectAccess.ObjectCode = Objects.Code
                        WHERE ObjectAccess.UserCode = Users.Code
                        ORDER BY Objects.Name
                        FOR XML PATH ('')
                  )
                  , 1, 1, '')
      ) AS oa (ObjectList)

Results:

| code | UserName |                                                                   ObjectList |
|------|----------|------------------------------------------------------------------------------|
|    A |     Aaaa |                       Fred & Ginger Whizzbang,The diamond<> cutter |
|    B |     Bbbb |              Fred & Ginger Whizzbang,The diamond<> cutter,Whatever |
|    C |     Cccc | Blah de Blah,Fred & Ginger Whizzbang,The diamond<> cutter,Whatever |
|    D |     Dddd |                             Blah de Blah,The diamond<> cutter,Whatever |
|    E |     Eeee |                                                                       (null) |