how to use the group by with xml query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to use the group by with xml query

Author

Topic

sudhirbharti
Starting Member

16 Posts

Posted - 2008-09-04 : 06:05:23

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 06:26:28

[code]SELECT t.ID, t.DATE,LEFT(il.IssueList,LEN(il.IssueList)-1)
FROM (SELECT DISTINCT ID, DATE FROM YourTable)t
CROSS APPLY (SELECT ISSUE + ',' AS [text()]
FROM YourTable
WHERE ID=t.ID
AND DATE = t.DATE
FOR XML PATH('')) il(IssueList)[/code]

sudhirbharti
Starting Member

16 Posts

Posted - 2008-09-04 : 06:53:31

thanks its working!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources