Click here to Skip to main content
15,064,806 members
Please Sign up or sign in to vote.
4.00/5 (3 votes)
Hello All,

I have a table in SQL Server2000 like as below -

Approver	Valid from	Txn Used
Ramya	        06.12.2012	MASS,MEMASSPO
Partha	        12.12.2012	MB22, SE16, MD04
Suveen Mohan	20.12.2012	SM30, SE16N, SE16
Suveen Mohan	22.12.2012	KP26
Suveen Mohan	23.12.2012	SE11_OLD
Uma	        26.12.2012	WE20
Suveen Mohan	27.12.2012	SE16, VA03, VOV5

and need to convert it to in below format.

Approver	Valid from	Txn Used
Ramya	        06.12.2012	MASS
Ramya	        06.12.2012	MEMASSPO
Partha	        12.12.2012	MB22
Partha	        12.12.2012	SE16
Partha	        12.12.2012	MD04
Suveen Mohan	20.12.2012	SM30
Suveen Mohan	20.12.2012	SE16N
Suveen Mohan	20.12.2012	SE16
Suveen Mohan	22.12.2012	KP26
Suveen Mohan	23.12.2012	SE11_OLD
Uma	        26.12.2012	WE20
Suveen Mohan	27.12.2012	SE16
Suveen Mohan	27.12.2012	VA03
Suveen Mohan	27.12.2012	VOV5

Do any one have any idea about this.

Thanks Regards
Suveen Mohan

[edit]Code block added - OriginalGriff[/edit]
Updated 12-Jan-13 2:43am
Tharaka MTR 12-Jan-13 8:39am
Could you please give us what are the columns in the table?
is it |Approver|Valid From|Txn Used| ?
and data in TxnUsed store as comma separated?
suveenmohan 13-Jan-13 12:08pm
Yes, we have three columns 1- Approver 2- [Valid From] 3-TxnUsed
TxnUsed contains comma separated data.

Thanks & Regards,
Suveen Mohan

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 (
        CAST('<r>' + REPLACE(CAST (TxnUsed as varchar(MAX)), ',', '</r><r>') + '</r>' AS XML) AS TxnUsed
    FROM mytbl
    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[^]
suveenmohan 13-Jan-13 5:24am

Thanks, but it doesn't work in SQL Server2000.

suveenmohan 15-Jan-13 11:19am
Thanks it worked...

Tharaka MTR 15-Jan-13 12:25pm
he he ... if you found the answer, then mark it to close this thread.
Since you're using Sql Server 2000 I think the easiest option is to create a small stored procedure which loops through the rows. For each row you iterate through the values in Tn Used and if you find multiple values separated by a comma, insert each one in a temporary table along with approver and valid from. When all this has been done, do a select to the temporary table so that the set will return to the calling program.

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