How to return one row but concatenate the reason column

Question

I have an sql server 2012 database table which contains data just like below:

id    import_id    tenancyname    owner    reason
----- ----------   ------------   -----    ------
1      1            test          null     Owner is missing
2      2            null          null     Tenancy Name is Missing
2      2            null          null     Owner is Missing

As you can see from the data above there are 2 reasons for row id 2, because there are 2 things wrong with that row.

Now what im trying to do is to only return the row once but concatenate the reasons into 1 cell so it will look like the following:

id    import_id    tenancyname    owner    reason
----- ----------   ------------   -----    ------
1      1            test          null     Owner is missing
2      2            null          null     Tenancy Name is Missing \newline Owner is Missing

help please from the experts would be greatly appreciated

Does SQL server support GROUP_CONCAT? If it does, do select id, import_id, tenancyname, owner, group_concat(reason) from tablename group by id, import_id, tenancyname, owner — jarlh, Jan 15 '15 at 13:16
Check this link: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — rasso, Jan 15 '15 at 13:17
Have a look at [this](http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation). — dario, Jan 15 '15 at 13:18

score 1 · Accepted Answer · answered Jan 15 '15 at 13:17

1

Try this:

SELECT A.id, A.import_id, A.tenancyname, A.owner, MAX(STUFF(fxMerge.reason, 1, 1, '')) 
FROM tableA A
CROSS APPLY(
    SELECT ',' + reason 
    FROM tableA A1
    WHERE A.import_id = A1.import_id 
    FOR XML PATH('')
) fxMerge (reason) 
GROUP BY A.id, A.import_id, A.tenancyname, A.owner

answered Jan 15 '15 at 13:17

Saharsh Shah

28,687
8
48
83

Yours works perfectly but how do i put a newline rather than a comma in the reason? This is so when it goes into excel the reasons are in a list – Stew Jan 15 '15 at 13:27

score 1 · Answer 2 · answered Jan 15 '15 at 13:19

This question has answers, but there is a "right" way to approach it. Some of the answers are incomplete.

You want:

select id, import_id, tenancyname, owner,
       stuff((select '
' + reason
             from table t2
             where t2.id = t.id
             for xml path ('concat'), type
            ).value('/concat[1]', 'varchar(max)'),
            1, 1, '')
from table t
group by id, import_id, tenancyname, owner;

It is important to use value to pull the value out, because this fixes the problem of characters getting converted to xml equivalents (& instead of &, for instance).

How to return one row but concatenate the reason column

2 Answers2