Create comma separated values while join in SQL Server

Question

I have prepared a demo where I paste my table structures and select query Here. Here one question can have multiple topics and for that reason multiple topics are displaying while join.

But I want something like this,

question_id | QuestionText                            | QuestionType    | topicName
------------+-----------------------------------------+-----------------+-------------------------
1           | First true false question from fronend. | True/False      | First Law,newton's law,...

I have tried my best as mentioned here to achieve:

How to concatenate text from multiple rows into a single text string in SQL server?

But could't be able to get my desired result.

I am using SQL Server 2014.

Answer 1

you can use stuff() function after converting the column value to xml

select distinct t1.question_id,
        t1.description AS [QuestionText],
        t2.code AS [questionType],
        STUFF(( SELECT ',' +
            td.description
        FROM dbo.question_master qm
            INNER JOIN dbo.question_type qt
                ON qm.question_type_id = qt.question_type_id
                INNER JOIN dbo.question_topics qt1
                ON qt1.question_id = qm.question_id
            INNER JOIN dbo.topic_details td
            ON td.topicid = qt1.topic_id        
        WHERE qm.question_id = t.question_id FOR XML PATH('')), 1, 1, '') as topic_name
from question_master t
inner join question_master t1 on t1.question_id = t.question_id
inner join question_type t2 on t2.question_type_id = t1.question_type_id