SQL - combine multiple rows in to a single as a list

Question

How to combine/merge multiple rows into a single rows as a list in SQL.

[original Scenario:]

[Required Scenario:]

Possible duplicate of [Concatenate many rows into a single text string?](https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Caius Jard, Nov 28 '17 at 12:01
Please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer — , Nov 28 '17 at 12:36

Yogesh Sharma · Answer 1 · 2017-11-28T13:01:47.560

SQL Server provides ROW_NUMBER() function to achieve the above

SELECT CASE WHEN ROW_NUMBER() OVER(PARTITION BY [column1] ORDER BY [column1]) > 1
            THEN ''
            ELSE CAST([column1] AS VARCHAR)
        END [column1],
        [column2]
FROM <table>;

EDIT : use of STUFF() function

select 
       [column1],
       [column2] = stuff(
                          (select DISTINCT ' '+[column2] from <table> where [column1] = t.[column1] for xml path('')),
                          1,1, ''
                    )
        from <table> t group by [column1]

Result :

column1 column2
1       Value 1 Value 2 Value 3
2       Value 4
3       Value 5 Value 6

this gives me separate rows in column2 for each value in column1, but I want single row in column2 for each value in column1. — Ayush Jain, Nov 28 '17 at 12:55

score 0 · Answer 2 · edited Nov 29 '17 at 04:14

thanks @Yogesh For your help !!

I have used the below query and it is working fine for me and displays the data as requiredrequired Scenario:

    Select distinct ST2.SubjectID, 
    substring(
        (
            Select CHAR(10) +ST1.StudentName  AS [text()]
            From dbo.Students ST1
            Where ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            For XML PATH ('')
        ), 2, 1000) [Students]
From dbo.Students ST2

SQL - combine multiple rows in to a single as a list

2 Answers2