How to present row data in Comma seperated list when multiple table joins involved in SQL Server 2014

Question

I want to present multiple rows data for a particular Id in comma separated list. If it has only one join I have no problem to display but when it includes many tables it is not presented properly.

My data is as follows.

Declare @EmpClass Table(ClassId varchar(10),EmpId int)
INSERT INTO @EmpClass
Values('A',1)
,('B',2)
,('C',3)

Declare @Employees Table (EmpId int, EmpName Varchar(100))
INSERT INTO @Employees
VALUES(1,'RAM')
,(2,'RAJ')
,(3,'LAXMAN')

Declare @EmpSubjects Table (EmpId int, SubjectId int)
INSERT INTO @EmpSubjects 
VALUES(1,1)
,(1,2)
,(1,3)
,(2,1)
,(3,1)
,(3,2)

    Declare @Subjects Table (SubjectId int, Subject Varchar(100))
    INSERT INTO @Subjects
    VALUES(1,'Maths')
    ,(2,'Science')
    ,(3,'Physics')
    ,(4,'Physics')
    ,(5,'Maths')
    ,(6,'Physics')

I have tried the below code and got the below result

SELECT EC.ClassId,E.EmpId
    ,ES.SubjectId,Subject
FROM @EmpClass EC
LEFT JOIN @Employees E ON EC.EmpId=E.EmpId
LEFT JOIN @EmpSubjects ES ON E.EmpId=ES.EmpId
LEFT JOIN @Subjects S ON S.SubjectId=ES.SubjectId
WHERE E.EmpId=1

I got the below result

ClassId EmpId   SubjectId   Subject
A         1       1         Maths
A         1       2         Science
A         1       3         Physics

The result needed as follows.

ClassId   EmpId SubjectId    Subject
A           1       1         {"1":"Maths","2":"Science","3":"Physics"}

I appreciate your help for this.

Thanks

Duplicate of https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv — Salman A, Mar 06 '20 at 09:56
Does this answer your question? [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — Panagiotis Kanavos, Mar 06 '20 at 18:01

score 0 · Answer 1 · answered Mar 06 '20 at 10:10

The comma-separated data in your expected result looks like JSON to me. Have you tried something like this?

SELECT EC.ClassId,E.EmpId,
    Subject = (
        SELECT [1], [2], [3], [4], [5], [6]
        FROM (
            SELECT S.SubjectID, S.Subject
            FROM @EmpSubjects ES
            LEFT JOIN @Subjects S ON S.SubjectId=ES.SubjectId
            WHERE ES.EmpId=E.EmpId
        ) as Src
        PIVOT (
            MAX([Subject])
            for [SubjectID] in ([1], [2], [3], [4], [5], [6])
        ) as p
        FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
    )
FROM @EmpClass EC
LEFT JOIN @Employees E ON EC.EmpId=E.EmpId
WHERE E.EmpId=1

Which yields the result:

ClassId  EmpId  Subject
-------- ------ -----------------------------------------
A        1      {"1":"Maths","2":"Science","3":"Physics"}

I need this in Sql Server 2014 at least with comma separated instead of json — Madhu, Mar 06 '20 at 10:40

score 0 · Answer 2 · answered Mar 06 '20 at 17:35

With 2014 it's a little bit more of a challange but here's an option using the xml functions:

SELECT  [Results].[ClassId]
       , [Results].[EmpId]
       , '{' + STUFF((SELECT ',"' + CONVERT(VARCHAR(2), tb.[SubjectId]) + '":"' + CAST(tb.[Subject] AS VARCHAR(MAX)) + '"'
                   FROM   (
                              SELECT    [EC].[ClassId]
                                      , [E].[EmpId]
                                      , [ES].[SubjectId]
                                      , [S].[Subject]
                              FROM      @EmpClass [EC]
                              LEFT JOIN @Employees [E]
                                  ON [EC].[EmpId] = [E].[EmpId]
                              LEFT JOIN @EmpSubjects [ES]
                                  ON [E].[EmpId] = [ES].[EmpId]
                              LEFT JOIN @Subjects [S]
                                  ON [S].[SubjectId] = [ES].[SubjectId]
                          ) AS [tb]
                   WHERE  ( [tb].[ClassId] = [Results].[ClassId] AND tb.[EmpId] = [Results].[EmpId] )
                   FOR XML PATH(''), TYPE
               ).[value]('(./text())[1]', 'VARCHAR(MAX)'), 1, 1, '') + '}' AS [Subjects]
FROM     (
             SELECT    [EC].[ClassId]
                     , [E].[EmpId]
                     , [ES].[SubjectId]
             FROM      @EmpClass [EC]
             LEFT JOIN @Employees [E]
                 ON [EC].[EmpId] = [E].[EmpId]
             LEFT JOIN @EmpSubjects [ES]
                 ON [E].[EmpId] = [ES].[EmpId]
         ) [Results]
WHERE     [Results].[EmpId] = 1
GROUP BY [Results].[ClassId], [Results].[EmpId]

Giving you the results of:

ClassId    EmpId       Subjects
---------- ----------- -------------------------------------------
A          1           {"1":"Maths", "2":"Science", "3":"Physics"}

The first sub-query is converting to XML using the FOR XML, so we can basically aggregate the columns. Then STUFF() to remove the leading "," from it. Then wrap that with "{}"

How to present row data in Comma seperated list when multiple table joins involved in SQL Server 2014

2 Answers2