通过唯一标识符组合查询结果的行? [英] Combining rows of queried results by unique identifier?

问题描述

我有一个查询，该查询搜索多个表并为查询表之一的特定列中的每个值返回一行.该表为一个唯一标识符返回多行.我想做的是合并具有相同唯一标识符的那些行，并合并以逗号分隔的列值中的2个，并将这些值作为唯一列返回.

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.

示例:

 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

我需要将此查询转换为可在C#程序中使用的存储过程.起初，我只是按原样使用数据，并使用C#使用数组和某些逻辑来组合行，但是我必须将其存储为存储过程，以使数据移交给已经排序和组合的C#程序.我不想，我必须.我需要帮助.

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.

任何人都可以帮忙吗?

推荐答案

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;

这篇关于通过唯一标识符组合查询结果的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！