Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL2000 SQL-Server
Hi All,
 
My requirement is to write a query in SQL Server 2000. Scenario is:
 
Suppose a table has two columns ID(not primary key) and Name. Values are like-
ID Name
1 A
1 B
1 C
2 D
2 E
 
then I have to query this table to get the output like
ID Name
1 A,B,C
2 D,E
 
I have written a query for the same using FOR XML PATH but that does not supported in SQL Server 2000 but works good in 2005/2008. Also I tried to put some extra logic to get this output but it results in bad performance as the input table is huge. But using FOR XML PATH query on the same table in 2005 it takes to time to output.
 
So, please let me know what can be the possible replacement of FOR XML PATH in 2000.
I tried XML AUTO but that is giving XML results so please write the query if your suggestion is to use XML AUTO.
 
Any help is highly appreciated.
 
Thanks,
Ankit J.
Posted 27-Jan-13 23:35pm
Comments
CHill60 at 28-Jan-13 5:56am
   
how about FOR XML RAW? PATH isn't supported in 2000
Member 9748847 at 28-Jan-13 7:39am
   
@CHil60 Tried XML RAW. Its giving result in the XML format. Something like:
ID NAME
1
2
 
If you think XML RAW will work, can you please write a simple query that will give the required output.
 
Thanks in advance.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

See my article below, I have explained most of the possibilities.
 
Generating and splitting a delimited string column[^]
  Permalink  
Comments
Member 9748847 at 29-Jan-13 4:37am
   
@Tharaka
The solution is correct but there is a huge performance overhead.
For the same no of output recores, using FOR XML PATH in 2005/2008 takes no time while COALESCE taking 23-26 sec.
 
This time difference is un-affordable and it makes the application to throw exceptions.
 
I tried with FOR XML AUTO in 2000, it takes no time but the problem in its giving records for the concatenated column in XML format.
Like:
ID NAME
1
2
 
Please suggest.
Tharaka MTR at 29-Jan-13 8:12am
   
Yep, But As far as I know this is the best and easiest way. You have to use some other optimization mechanize to optimize this query. Some experts said that ISNULL is bit faster than COALESCE.
 
May be I'm wrong. :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 CPallini 345
1 BillWoodruff 324
2 George Jonsson 279
3 Sergey Alexandrovich Kryukov 258
4 OriginalGriff 227
0 OriginalGriff 5,050
1 CPallini 4,225
2 Sergey Alexandrovich Kryukov 3,639
3 George Jonsson 2,911
4 Gihan Liyanage 2,386


Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 28 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100