CODEWITHSUNDEEP
sqlsql-servert-sqlselectone-to-many
109221793
109221793

Reputation: 16887

MS SQL - One to Many Relationship - Need to return single row

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

Upvotes: 2

Views: 7961

Answers (1)

GarethD
GarethD

Reputation: 69749

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

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

If you want to return a variable number of values then you will need to use dynamic 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

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.

Upvotes: 4

Related Questions