Concatenate multiple columns into one column (,) separated

Question

In SQL database that stores Images in a parcelImages table.Since each parcel can take part in multiple Images and each images can involve multiple parcels.

I want to get each parcel having Image value with comma separated values as shown above

I tried with this SQL Query

SELECT ParcelId,id,concat(ParcelId, ' ', id) 
FROM ParcelImages 
WHERE Id IN (43418,43464,43465,43466,43467,43470,43471,43482,43483)

but expected result not get. How can this be done?

I have also tried an INNER JOIN How can I achieve this, I'm kinda stuck at the early beginning.

If there is a table called STUDENTS

ID                     StudentName                                                      
----------             -------------                                                      
1                      Mary                                                      
1                      John                                                      
1                      Sam                                                      
2                      Alaina                                                      
2                      Edward

Result I expected was:

ID              StudentName                                                      
----------      -------------                                                      
1               Mary, John, Sam                                                      
2               Alaina, Edward

score 1 · Answer 1 · answered Feb 07 '17 at 14:52

1

I think you need to use GROUP CONCAT (only available in MySQL). If you are using MS SQL Server you can try this project: https://groupconcat.codeplex.com/

answered Feb 07 '17 at 14:52

renyalvarado

111
1

Thomas Rushton · Answer 2 · 2017-02-07T16:03:28.703

You could do this using the XML PATH concatenation trick, like so:

DECLARE @ParcelImage TABLE
(
    ParcelID INT,
    ImageID INT
);

INSERT INTO @ParcelImage
(
    ParcelID,
    ImageID
)
VALUES
(10854, 43418),
(23495, 43464),
(23495, 34365),
(23495, 43466),
(23495, 43467),
(32239, 43470),
(32239, 43471),
(32239, 43472);

SELECT *
FROM @ParcelImage AS pim;


SELECT DISTINCT
    CONVERT(VARCHAR(10), pim.ParcelID) + '\'
    + STUFF(
               (
                   SELECT ',' + CONVERT(VARCHAR(10), ImageID)
                   FROM @ParcelImage pi2
                   WHERE pi2.ParcelID = pim.ParcelID
               FOR XML PATH(''), TYPE
               ).value('.', 'varchar(max)'),
               1,
               1,
               ''
           )
FROM @ParcelImage pim;

edit: I've added the XML tidying-up bit - the line ).value('.','varchar(max)'), was just ),...

Aaron Bertrand has some notes about this technique in his post on SQL Server Grouped Concatenation; there's a similar discussion in this Simple-Talk post

Concatenate multiple columns into one column (,) separated

2 Answers2