sql - Combining rows of queried results by unique identifier?

Question

I have a query that searches through several tables and returns one row for every value in one specific column of one of the queried tables. The table returns multiple rows for one unique identifier. What I want to do is combine those rows that have the same unique identifier and combine 2 of the column's value separated by commas and return those values as a unique column.

Example:

 Museum     MuseumID     Country     City     Paintings     Sculptures

 Louvre     345          France      Paris    Mona Lisa     NULL
 Louvre     345          France      Paris    NULL          Venus De Milo
 Louvre     345          France      Paris    Ship of Fools NULL

Instead I would like to make the query do this:

 Museum     MuseumID     Country     City     Art
 Louvre     345          France      Paris    Mona Lisa, Venus De Milo, Ship of Fools

I need to turn this query into a stored procedure that can be used in a C# program. At first I just took the data as is and used C# to combine the rows using arrays and some logic but I HAVE TO make this a stored procedure instead to make the data come over to the C# program already sorted and combined. I don't want to I have to. I need help.

Can anyone help with this?

Answer 1

DECLARE @a TABLE
(
    Museum VARCHAR(32),
    MuseumID INT, 
    Country VARCHAR(32),
    City VARCHAR(32),
    Paintings VARCHAR(32),
    Sculptures VARCHAR(32)
);

INSERT @a VALUES
('Louvre',345,'France','Paris', 'Mona Lisa',     NULL),
('Louvre',345,'France','Paris', NULL,            'Venus De Milo'),
('Louvre',345,'France','Paris', 'Ship of Fools', NULL);


SELECT DISTINCT Museum, MuseumID, Country, City, 
    Art = STUFF((SELECT ', ' + COALESCE(Paintings, Sculptures, '')
    FROM @a AS a2
    WHERE a2.museum = a.museum AND a2.MuseumID = a.MuseumID
    AND a2.Country = a.Country AND a2.City = a.City
    FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),
    1,2,'')
FROM @a AS a;