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

Answer 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