Concatenate strings

0

Please login or register to vote for this query.

(click on this box to dismiss)

concatenating strings using for xml path('')

Stack Overflow

Q&A for programmers

-- Concatenate strings
-- concatenating strings using for xml path('')

;with boxes(id, name) as
(
select 1,   'Green Box' union all
select 2,   'Red Box' union all
select 3,   'Yellow Box'
),
fruits(id, name) as
(
select 1,   'apple' union all
select 2,   'orange'
),
fruitboxes(boxID, fruitID) as
(
select 1,      1 union all
select 1,      2 union all
select 2,      1 union all
select 2,      2 union all
select 3,      1
)

select
  b.id,
  b.name,
  stuff((select ','+f.name
         from fruits as f 
           inner join fruitboxes as fb
             on f.id = fb.fruitID
         where fb.boxID = b.id
         for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '') as Fruits
from boxes as b

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached