Click here to Skip to main content
11,486,023 members (74,699 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL2000
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]
Posted 12-Jan-13 3:08am
Edited 12-Jan-13 3:43am
v2
Comments
Tharaka MTR at 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 at 13-Jan-13 12:08pm
   
Hello,
Yes, we have three columns 1- Approver 2- [Valid From] 3-TxnUsed
TxnUsed contains comma separated data.

Thanks & Regards,
Suveen Mohan
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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[^]
  Permalink  
v3
Comments
suveenmohan at 13-Jan-13 5:24am
   
Hi,

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

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

Suveen
Tharaka MTR at 15-Jan-13 12:25pm
   
he he ... if you found the answer, then mark it to close this thread.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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.
  Permalink  

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

  Print Answers RSS
0 C#developer.asp 429
1 Sergey Alexandrovich Kryukov 375
2 Maciej Los 365
3 Mehdi Gholam 250
4 OriginalGriff 144
0 Sergey Alexandrovich Kryukov 8,819
1 OriginalGriff 8,047
2 Sascha Lefèvre 3,199
3 Maciej Los 2,886
4 Richard Deeming 2,370


Advertise | Privacy | Mobile
Web01 | 2.8.150520.1 | Last Updated 17 Jan 2013
Copyright © CodeProject, 1999-2015
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