DECLARE @foos VARCHAR(4000)
SELECT @foos = COALESCE(@foos + ',', '') + Foo FROM Bar
SELECT @foos AS Foo
LukeH
2009-02-25 15:46:48
DECLARE @foos VARCHAR(4000)
SELECT @foos = COALESCE(@foos + ',', '') + Foo FROM Bar
SELECT @foos AS Foo
Ross,
this should get you started.
DECLARE @r VARCHAR(8000)
SELECT @r = (SELECT DISTINCT Foo + ', ' FROM Bar FOR XML PATH(''))
IF @r IS NOT NULL AND @r <> '' SET @r = SUBSTRING(@r, 1, LEN(@r)-1)
SELECT @r
Try the following
declare @joined varchar(max)
set @joined = ''
select @joined = @joined + IsNull(Foo + ',', '')
from Bar (nolock)
--; Drop last "," if necessary
set @joined = substring(@joined, 1, len(@joined) - (case when len(@joined) > 0 then 1 else 0 end))
select @joined as foo
SELECT
(
SELECT
CASE
WHEN ROW_NUMBER() OVER(ORDER BY bar) = 1 THEN ''
ELSE ', '
END + CAST(bar AS VARCHAR)
FROM foo
ORDER BY bar
FOR XML PATH('')
)
select max(a),max(b),max(c) from
(
select 'a' as a,null as b,null as c
union
select null,'b',null
union
select null,null,'c'
) as x