how to use the group by with xml query

[sudhirbharti]

We have a need to see if data exists in a field based on an ID and Date. The data we have exists in a table as follows:

ID DATE ISSUE
903 01/15/08 3,2,1,,,,1,
903 01/15/08 3,,1,,,,,1,
903 01/16/08 ,,,,,,,,1,,
903 01/16/08 ,,5,,,,4,,,
903 01/15/08 ,,,,,,,,,
904 01/15/08 ,,,,,,,,,
904 01/15/08 ,,,,,,,,,
904 01/15/08 ,,,,,,,,,
904 01/16/08 ,,,,,,,,,

each record reflects a "snapshot" of data for a specific ID and a specific time/date

What we want to do is combine all the issues for a specific ID and Date, so that we have the following:

ID DATE ISSUE
903 01/15/08 3,2,1,,,,1,3,,1,,,,,1,,,,,,,,,1,,,,,,,,,,,,,,,,,,,,
903 01/16/08 ,,,,,,,,1,, ,,5,,,,4,,,
904 01/15/08 ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
904 01/16/08 ,,,,,,,,,

We have been trying the following code, but unfortunately it is making the ISSUE field identical for every ID.

create table #temp(ID int, DATE datetime, ISSUE varchar(255)) 
insert into #temp values(903, '01/15/08', '3,2,1,,,,1,') insert into #temp values(903, '01/16/08', ',,,,,,,,1,,') insert into #temp values(903, '01/16/08', ',,5,,,,4,,,') insert into #temp values(903, '01/15/08', '3,,1,,,,,1,') insert into #temp values(903, '01/15/08', ',,,,,,,,,') insert into #temp values(904, '01/15/08', ',,,,,,,,,') insert into #temp values(904, '01/15/08', ',,,,,,,,,') insert into #temp values(904, '01/15/08', ',,,,,,,,,') insert into #temp values(904, '01/16/08', ',,,,,,,,,')

SELECT distinct id ,date,ISSUE= SUBSTRING( ( SELECT ',' + ISSUE AS [text()] FROM #temp1 T1 WHERE T1.ID = T2.ID FOR XML PATH(''), ELEMENTS ), 3, 1000 ) into #temp4 FROM #temp1 T2

now we want the record by date and add the issue by group date and for each site like this

ID DATE ISSUE
903 01/15/08 3,2,1,,,,1,3,,1,,,,,1,,,,,,,,,1,,,,,,,,,,,,,,,,,,,,
903 01/16/08 ,,,,,,,,1,, ,,5,,,,4,,,
904 01/15/08 ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
904 01/16/08 ,,,,,,,,,

Any Ideas?