sql server - Concatenate rows from a complex select in SQL -

sql server - Concatenate rows from a complex select in SQL -

rdbms sql server 2008.

i have 3 tables. simplify 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 nomination. each nomination may have 1 nomination order. each nomination order may have many nomination order items, each of them references gift order.

i'm making study reporting services , need display info each nomination gift in single row, showing gift names concatenated.

currently looks this:

nominationid | nominationorderid | giftname 5 | 1 | tvset 5 | 1 | tabletpc 5 | 1 | littleponny

i need this:

nominationid | nominationorderid | giftname 5 | 1 | tvset, tabletpc, littleponny

a simplified illustration of current sql query:

select nn.nominationid ,n_o.nominationorderid ,g.name giftname dbo.nomination nn left bring together dbo.nominationorder n_o on n_o.nominationid = nn.nominationid left bring together dbo.nominationorderitem noi on noi.nominationorderid = n_o.nominationorderid left bring together dbo.gift g on g.giftid = noi.giftid

how can rewrite create output in single string , concatenate gift names?

you can utilize cte :

with ctetbl (nominationid, nominationorderid, giftname) ( query here)

and concatenate rows same nominationid , nominationorderid for xml path('') , after replace first comma , stuff:

select t.nominationid , t.nominationorderid , stuff( ( select ', ' + giftname ctetbl nominationid = t.nominationid , nominationorderid = t.nominationorderid order giftname desc xml path('') ), 1, 1, '') ctetbl t grouping t.nominationid , t.nominationorderid sqlfiddle

sql sql-server sql-server-2008 concatenation