2

This question already has an answer here:

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.

enter image description here

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                                                      

marked as duplicate by Kin Shah, mustaccio, LowlyDBA, RLF, Mikael Eriksson Feb 7 '17 at 18:48

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    What version of SQL Server are you using? – Tom V Feb 7 '17 at 15:24
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/

0

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.