Click here to Skip to main content
14,733,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.

Ankit J.
CHill60 28-Jan-13 5:56am
how about FOR XML RAW? PATH isn't supported in 2000
Member 9748847 28-Jan-13 7:39am
@CHil60 Tried XML RAW. Its giving result in the XML format. Something like:
1 <row name="A"><row name="B"><row name="C">
2 <row name="D"><row name="E">

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

See my article below, I have explained most of the possibilities.

Generating and splitting a delimited string column[^]
Member 9748847 29-Jan-13 4:37am
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.
1 <e name="A"><e name="B"><e name="C">
2 <e name="D"><e name="E">

Please suggest.
Tharaka MTR 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)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900