CODEWITHSUNDEEP
sql-servert-sqlgroup-by
Petr
Petr

Reputation: 1219

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

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

Upvotes: 1

Views: 1632

Answers (1)

M.Ali
M.Ali

Reputation: 69564

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    ║
╚═══════╩═════════╝

Upvotes: 2

Related Questions