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-
then I have to query this table to get the output like
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.