Concatenate rows from a complex select in SQL

Question

RDBMS is SQL Server 2008.

I have 3 tables. To simplify they look like this:

NominationOrder table:

NominationOrderId | NominationId
         1        |      5
         2        |      9

NominationOrderItem table:

NominationOrderItemId | NominationOrderId | GiftId
           1          |         1         |    6
           2          |         1         |    3
           3          |         1         |    9

Gift table:

GiftId | GiftName |
  3    |   TVSet
  6    |  TabletPC
  9    | LittlePonny

So, there's some Nomination. Each Nomination may have 1 Nomination Order. Each Nomination Order may have many Nomination Order Items, each of them references to some Gift from this order.

I'm making a report for Reporting Services and I need to display data about each nomination with Gift in a single row, showing Gift names concatenated.

Currently it looks like this:

NominationId | NominationOrderId |    GiftName
      5      |          1        |     TVSet     
      5      |          1        |    TabletPC    
      5      |          1        |   LittlePonny

I need it to look like this:

NominationId | NominationOrderId |           GiftName
      5      |          1        |   TVSet, TabletPC, LittlePonny

A simplified example of current SQL query:

  select 
    nn.NominationId
    ,n_o.NominationOrderId
    ,g.name GiftName
from dbo.Nomination nn
    LEFT JOIN dbo.NominationOrder n_o ON n_o.NominationId = nn.NominationId
    LEFT JOIN dbo.NominationOrderItem noi ON noi.NominationOrderId = n_o.NominationOrderId
    left join dbo.Gift g on g.GiftId = noi.GiftId

How can I rewrite it to make an output in a single string and concatenate gift names?

http://stackoverflow.com/questions/10553198/merge-multiple-rows-into-a-single-row/10553404#10553404 — SQLMason, Jun 22 '14 at 00:13

score 3 · Answer 1 · answered Jun 21 '14 at 15:47

SELECT result.NominationId, Substring(result.Gifts,3,Len(result.Gifts) - 3)
FROM (
    SELECT 
    NominationId, 
    (SELECT ', ' + CAST(g.GiftName AS varchar(MAX)) 
        FROM NominationOrder i 
        INNER JOIN NominationOrderItem noi on i.NominationOrderId = noi.NominationOrderId
        INNER JOIN Gift g on noi.GiftId = g.GiftId
        WHERE i.NominationId = n.NominationId
        FOR XML PATH (''))
         AS Gifts
    FROM NominationOrder n
    GROUP BY NominationId
) result

potashin · Accepted Answer · 2014-06-21T19:04:54.747

You can use CTE :

WITH cteTbl (NominationId, NominationOrderId, GiftName) AS ( Your Query here)

And then concatenate all rows with the same NominationId and NominationOrderId with FOR XML PATH('') and after that replace the first comma , with STUFF:

SELECT t.NominationId
     , t.NominationOrderId
     , STUFF( ( SELECT ', ' + GiftName
                FROM cteTbl
                WHERE NominationId = t.NominationId
                  AND NominationOrderId = t.NominationOrderId
                ORDER BY GiftName DESC
                FOR XML PATH('') ), 1, 1, '')
FROM cteTbl t 
GROUP BY t.NominationId
       , t.NominationOrderId

Concatenate rows from a complex select in SQL

2 Answers2

SQLFiddle