Please check whether following query is ok for you, I have no SQL Server 2000 installed with me. I execute this by changing compatibility mode of SQL 2008 to SQL 2000. It works.
I assumed
TxnUsed is a
TEXTcolumn
;WITH cte AS (
SELECT
Approver,
ValidFrom,
CAST('<r>' + REPLACE(CAST (TxnUsed as varchar(MAX)), ',', '</r><r>') + '</r>' AS XML) AS TxnUsed
FROM mytbl
)
SELECT
Approver,
ValidFrom,
x.i.value('.', 'VARCHAR(MAX)') AS TxnUsed
FROM cte
CROSS APPLY TxnUsed.nodes('//r') x(i)
if it is a VARCHAR column then modify the following line
CAST('<r>' + REPLACE(TxnUsed, ',', '</r><r>') + '</r>' AS XML) AS TxnUsed
Try this for SQL 2000
SELECT a.Approver,
SUBSTRING(',' + a.TxnUsed + ',', n.Number + 1, CHARINDEX(',', ',' + a.TxnUsed + ',', n.Number + 1) - n.Number - 1) AS [Value]
FROM mytbl AS a
INNER JOIN master..spt_values AS n ON SUBSTRING(',' + a.TxnUsed1 + ',', n.Number, 1) = ','
WHERE n.Type = 'p'
AND n.Number > 0
AND n.Number < LEN(',' + a.TxnUsed + ',')
See my article for more info
Generating and splitting a delimited string column[
^]
Submit an article or tip