query to xml question

I have a table that contains 3 columns SiteID, Results and date. the table has 4 rows. I want to query the table and end up with 1 row that combines all the field into in the Results Column.

so in table form it looks like

627 test 3/3/7
627 bob 3/3/7
627 tom 3/9/7
627 rob 3/8/7

I want the resulting query to bring back one row:

test,bob,tom,rob

the following query will do 90% of what I want,

SELECT test +','
FROM #temp1
FORXMLPATH('')

BUT I cannot figure out how to provide a column name for the query result. instead I appear to get a guid of XML_F52E2B61-18A1-11d1-B105-00805F49916B

Q - is there a way to name the column, or is there a different way to create this query result without using XML?

I am us

Jim:

The best way is to make your select statement into a "derived table" or a correlated subquery; For example:

create table #temp1
( SiteID integer,
Results varchar(10),
date datetime
)
insert into #temp1 values (627, 'billy joe', '3/3/7')
insert into #temp1 values (627, 'bob', '3/3/7')
insert into #temp1 values (627, 'tom', '3/9/7')
insert into #temp1 values (627, 'rob', '3/8/7')

select distinct
siteId,
replace(replace(
( select replace (x.results, ' ', '~') as [data()]
from #temp1 x
where x.siteId = x.siteId
order by date
for xml path ('')
), ' ', ','), '~', ' ') as dataLabel
from #temp1 a

-- siteId dataLabel
-- --
-- 627 billy joe,bob,rob,tom

go

drop table #temp1
go

|||

selectcast((SELECT test +','FROM #temp1 FORXMLPATH(''))asvarchar(max))as YourName

|||I think I like Konstantin's better.|||

Thanks to both of you for the quick reply, they both work, but think I will use Konstantin's

|||

Actually I now have a different problem:

I am using

select siteid, Cast((SELECT Anomalies+ ',' FROM #temp1 FOR XML PATH('')) as varchar(max) ) as Anomaly from #temp1

this does work, sort of.... But it creates the result for all records in the table. I need it to create a seperate record for each siteid, otherwise all the resulting data is the same for all siteids?

any ideas?

|||Just add filter to subquery:
select siteid, Cast((SELECT Anomalies+ ',' FROM #temp1 where siteid=t.siteid FOR XML PATH('')) as varchar(max) ) as Anomaly from #temp1 t
sql