T-SQL GROUP BY - list instead of agregate function in the result

Question

Is there any way to use GROUP BY in T-SQL but get "join-list" as a result instead of agregate function? For example:

-- this table
CREATE TABLE tblDATA(
    Name int,
    GroupName nvarchar(50)
)

-- with this data
INSERT INTO tblDATA VALUES('Peter', 'A')
INSERT INTO tblDATA VALUES('Peter', 'B')
INSERT INTO tblDATA VALUES('Jane', 'A')
INSERT INTO tblDATA VALUES('Jane', 'C')
INSERT INTO tblDATA VALUES('Jane', 'D')
INSERT INTO tblDATA VALUES('Dave', 'B')
INSERT INTO tblDATA VALUES('Susan', 'E')
INSERT INTO tblDATA VALUES('Susan', 'F')


-- and get this query result in the two collumns (I don't care about delimiter) :
Peter       A, B
Jane        A, C, D
Dave        B
Susan       E, F

Check http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server — a1ex07, Dec 30 '13 at 20:26
Also read this: http://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server — Devart, Dec 31 '13 at 09:48

score 2 · Accepted Answer · answered Dec 30 '13 at 21:05

Test Data

CREATE TABLE #tblDATA(
    Name VARCHAR(20),     --<-- Your Name column is defined as INT Data Type 
    GroupName nvarchar(50)
)

-- with this data
INSERT INTO #tblDATA VALUES('Peter', 'A')
INSERT INTO #tblDATA VALUES('Peter', 'B')
INSERT INTO #tblDATA VALUES('Jane', 'A')
INSERT INTO #tblDATA VALUES('Jane', 'C')
INSERT INTO #tblDATA VALUES('Jane', 'D')
INSERT INTO #tblDATA VALUES('Dave', 'B')
INSERT INTO #tblDATA VALUES('Susan', 'E')
INSERT INTO #tblDATA VALUES('Susan', 'F')

Query

SELECT DISTINCT Name, STUFF(List.Groups, 1 ,2 , '') AS Groups
FROM #tblDATA t  
            CROSS APPLY (
                        SELECT ', ' + GroupName [text()]
                        FROM #tblDATA
                        WHERE Name = t.Name
                        FOR XML PATH('')
                        )List(Groups)

Result Set

╔═══════╦═════════╗
║ Name  ║ Groups  ║
╠═══════╬═════════╣
║ Dave  ║ B       ║
║ Jane  ║ A, C, D ║
║ Peter ║ A, B    ║
║ Susan ║ E, F    ║
╚═══════╩═════════╝

T-SQL GROUP BY - list instead of agregate function in the result

1 Answers1