sql COLUMNS TO ROW

Question

Iam tried to bring a column value string concat based on ID I wrote a sql but there seems to be some issue in that

table 1 having col1 and col2 table 2 having col1 and col3 table 3 having col1 col4 and col5

    COL1    COL2        COL3    COL4  COL5
    1000    APPLE        50     92    TESTING 
    1000    APPLE        50     92    ALPHA
    1000    APPLE        50     92    BETA
    1000    APPLE        50     92    OMEGA
    2000    ORANGE       60     90    DELTA
    2000    ORANGE       60     90    TEST 
    2000    ORANGE       60     90    SLEEP

I want output like BELOW

    1000 APPLE  50  92 TESTINGALPHABETAOMEGA
    2000 ORANGE 60 90  DELTATESTSLEEP

score 0 · Answer 1 · edited May 23 '17 at 12:21

You can use SQL Server's xml extensions to concatenate rows:

SELECT  COL1,
        COL2,
        COL3,
        COL4,
        MessageList = ( SELECT Col5 + ''
                        FROM    T1 AS T2
                        WHERE   T1.Col1 = T2.Col1
                        AND     T1.Col2 = T2.Col2
                        AND     T1.Col3 = T2.Col3
                        AND     T1.Col4 = T2.Col4
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)')
FROM    T1 
GROUP BY Col1, Col2, Col3, Col4;

Example on SQL Fiddle

For a more detailed explanation on how this works see this answer

EDIT

Below is an updated solution to show how this would work with 2 tables

SELECT  T1.COL1,
        T1.COL2,
        T2.COL3,
        T2.COL4,
        MessageList = ( SELECT  '' + Col5
                        FROM    T2 AS T3
                        WHERE   T1.Col1 = T3.Col1
                        AND     T2.Col3 = T3.Col3
                        AND     T2.Col4 = T3.Col4
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)')
FROM    T1 
        INNER JOIN T2
            ON T1.Col1 = T2.Col1
GROUP BY T1.Col1, T1.Col2, T2.Col3, T2.Col4;

Example on SQL Fiddle

I thought of avoiding joins and asking for simple example but i cannot integrate that in my code as Iam getting error as subquery is giving more than one result. So updated with the actual structure — user3271721, Feb 06 '14 at 17:53

score 0 · Answer 2 · answered Aug 21 '19 at 03:00

Assuming you have SQL server 2017+ you can use STRING_AGG

SELECT t1.col1, 
       col2,
       col3,
       col4
       STRING_AGG(Col5, CHAR(10)+CHAR(13)) WITHIN GROUP (ORDER BY col2)
FROM Table1 t1 join Table2 t2
on t1.Col1 = t2.Col1 join Table3 t3
on t1.Col1 = t3.Col1 join Table4 t4
on t1.col1 = t4.Col1 
GROUP BY t1.col1,col2,col3,col4

sql COLUMNS TO ROW

2 Answers2