MS SQL—一个到多个关系—需要返回单行

I have the following tables -

我有以下的表格

Search Result
----------------
SearchResultID PK
ProductID FK
SearchQuery
WebsiteName
URL
IsFound
CreatedOn
BatchID
Name

SearchResultItem
-----------------
SearchResultItemID PK
SearchResultID FK
Name
Value

These tables have a one to many relationship, so one Search Result, can have many Search Result Items.

这些表有一个到多个关系，所以一个搜索结果可以有很多搜索结果项。

I can do an INNER JOIN on these tables however that obviously gives one row per each Search Result Item. Ideally I would like one row per Search Result, for example...

我可以在这些表上做一个内部连接，但是显然每个搜索结果项都有一行。理想情况下，我希望每行搜索结果，例如……

SearchResultID | ProductID | SearchQuery | WebsiteName | URL | IsFound | 
CreatedOn | BatchID | Name | SearchResultItemID | Name 1 | Value 1 | Name 2 | 
Value 2 | Name 3 | Value 3 |

Is this possible to do? And if so, can someone point me in the right direction as to how I would do this - I think it would be something like this, only in ms-sql - one to many sql select into single row - mysql

这可能吗?如果是这样的话，谁能给我指明正确的方向，告诉我该怎么做呢?我想应该是这样的，只有在ms-sql中——从一个到多个sql选择到单行——mysql

1 个解决方案

#1

You can use the ROW_NUMBER() function to give each search result item a rank within each search result:

可以使用ROW_NUMBER()函数在每个搜索结果中为每个搜索结果项赋予一个级别:

SELECT  SearchResultItemID,
        SearchResultID,
        Name,
        Value,
        RowNumber = ROW_NUMBER() OVER(PARTITION BY SearchResultID ORDER BY SearchresultItemID)
FROM    SearchResultItem;

If you have a know number of items then you can use aggregate functions to get each name/value pair:

如果您知道项目的数量，那么您可以使用聚合函数获取每个名称/值对:

WITH RankedItem AS
(   SELECT  SearchResultItemID,
            SearchResultID,
            Name,
            Value,
            RowNumber = ROW_NUMBER() OVER(PARTITION BY SearchResultID ORDER BY SearchresultItemID)
    FROM    SearchResultItem
)
SELECT  SearchResultID,
        Name1 = MIN(CASE WHEN RowNumber = 1 THEN Name END),
        Value1 = MIN(CASE WHEN RowNumber = 1 then Value END),
        Name2 = MIN(CASE WHEN RowNumber = 2 THEN Name END),
        Value2 = MIN(CASE WHEN RowNumber = 2 then Value END),
        Name3 = MIN(CASE WHEN RowNumber = 3 THEN Name END),
        Value3 = MIN(CASE WHEN RowNumber = 3 then Value END),
        Name4 = MIN(CASE WHEN RowNumber = 4 THEN Name END),
        Value5 = MIN(CASE WHEN RowNumber = 4 then Value END)
FROM    RankedItem
GROUP BY SearchResultID;

You can then join this back to your Search result table giving a full query:

然后，您可以将此连接回您的搜索结果表，提供完整的查询:

WITH RankedItem AS
(   SELECT  SearchResultItemID,
            SearchResultID,
            Name,
            Value,
            RowNumber = ROW_NUMBER() OVER(PARTITION BY SearchResultID ORDER BY SearchresultItemID)
    FROM    SearchResultItem
), Items AS
(   SELECT  SearchResultID,
            Name1 = MIN(CASE WHEN RowNumber = 1 THEN Name END),
            Value1 = MIN(CASE WHEN RowNumber = 1 then Value END),
            Name2 = MIN(CASE WHEN RowNumber = 2 THEN Name END),
            Value2 = MIN(CASE WHEN RowNumber = 2 then Value END),
            Name3 = MIN(CASE WHEN RowNumber = 3 THEN Name END),
            Value3 = MIN(CASE WHEN RowNumber = 3 then Value END),
            Name4 = MIN(CASE WHEN RowNumber = 4 THEN Name END),
            Value4 = MIN(CASE WHEN RowNumber = 4 then Value END)
    FROM    RankedItem
    GROUP BY SearchResultID
)

SELECT  SearchResult.SearchResultID,
        SearchResult.ProductID,
        SearchResult.SearchQuery,
        SearchResult.WebsiteName,
        SearchResult.URL,
        SearchResult.IsFound,
        SearchResult.CreatedOn,
        SearchResult.BatchID,
        SearchResult.Name,
        Items.Name1,
        Items.Value1,
        Items.Name2,
        Items.Value2,
        Items.Name3,
        Items.Value3,
        Items.Name4,
        Items.Value4
FROM    SearchResult
        INNER JOIN Items
            ON SearchResult.SearchResultID = Items.SearchResultID;

Example on SQL Fiddle

示例SQL小提琴

If you want to return a variable number of values then you will need to use dynamic SQL:

如果您想返回一个可变数量的值，那么您将需要使用动态SQL:

DECLARE @SQL NVARCHAR(MAX) = '';

SELECT  @SQL = @SQL + ',[Name' + rn + '], [Value' + rn + '] '
FROM    (   SELECT  DISTINCT
                    rn = CAST(ROW_NUMBER() OVER(PARTITION BY SearchResultID ORDER BY SearchresultItemID) AS VARCHAR)
            FROM    SearchResultItem
        ) p;

SET @SQL = 'WITH RankedItem AS
            (   SELECT  SearchResultItemID,
                        SearchResultID,
                        Name,
                        Value,
                        RowNumber = ROW_NUMBER() OVER(PARTITION BY SearchResultID ORDER BY SearchresultItemID)
                FROM    SearchResultItem
            ), UnPivoted AS
            (   SELECT  upvt.SearchResultID,
                        Name = upvt.n + CAST(RowNumber AS VARCHAR),
                        upvt.v
                FROM    RankedItem
                        UNPIVOT
                        (   n
                            FOR v IN ([Name], [Value])
                        ) upvt
            ), Pivoted AS
            (   SELECT  *
                FROM    UnPivoted
                        PIVOT
                        (   MAX(V)
                            FOR Name IN (' + STUFF(@SQL, 1, 1, '') + ')
                        ) pvt
            )
            SELECT  SearchResult.SearchResultID,
                    SearchResult.ProductID,
                    SearchResult.SearchQuery,
                    SearchResult.WebsiteName,
                    SearchResult.URL,
                    SearchResult.IsFound,
                    SearchResult.CreatedOn,
                    SearchResult.BatchID,
                    SearchResult.Name' + @SQL + '                       
            FROM    SearchResult
                    INNER JOIN Pivoted
                        ON SearchResult.SearchResultID = Pivoted.SearchResultID;';

EXECUTE SP_EXECUTESQL @SQL;

Example on SQL Fiddle

示例SQL小提琴

N.B. I have intentionally used a different way of doing this in dynamic sql just to show there is more than one way to achieve the result of combining the rows.

N.B.我故意在动态sql中使用了一种不同的方式来实现这一点，只是为了表明有不止一种方法可以实现合并行的结果。