Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to Convert Semi colon Separated Values into Co

Author  Topic 

joshtheflame
Starting Member

5 Posts

Posted - 2013-03-07 : 00:23:01
I am working on a integration project where I am receiving three string parameters ItemCode ItemName Amount

and values will be in this format

ItemCod Parameter values
T1;T2;T3;

ItemName Parameter values
Pencil Box;Eraser;Mouse Pad;

Amount Paramter values
1900;2000;8900;

Now I would like to have a procedure in which i would receive these parameters and the query will convert this as column and records. I am not a SQLServer guy and someobody proposed the following solution but it is limited to only 2 records..I want a procedure and it should return rows depending upon how many strings seperated by semicolon.

here is the code iv got



INSERT INTO t
(cod, name)
VALUES
('T1;T2;T3;T4;T5;',
'Pencil Box;Eraser;Board Marker;Trimmer;Ball Point;');


SELECT
CAST('<r>'+REPLACE(cod,';','</r><r>')+'</r>' AS XML).query('/r[1]').value('.','varchar(256)') cod,
CAST('<r>'+REPLACE(name,';','</r><r>')+'</r>' AS XML).query('/r[1]').value('.','varchar(256)') name

INTO #tmpTable FROM t

insert INTO #tmpTable
SELECT
CAST('<r>'+REPLACE(cod,';','</r><r>')+'</r>' AS XML).query('/r[2]').value('.','varchar(256)') cod,
CAST('<r>'+REPLACE(name,';','</r><r>')+'</r>' AS XML).query('/r[2]').value('.','varchar(256)') name
FROM t;

select * from #tmpTable


scripts to create table

CREATE TABLE [dbo].[t](
[cod] [varchar](350) NULL,
[name] [varchar](300) NULL
) ON [PRIMARY]

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-07 : 00:46:29
[code]
SELECT a.code,b.name
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY t.cod ORDER BY cod) AS seq,
m.n.value('.','varchar(50)') as code
FROM (SELECT cod,CAST('<root><r>'+REPLACE(cod,';','</r><r>')+'</r></root>' AS XML) AS codxml,CAST('<root><r>'+REPLACE(name,';','</r><r>')+'</r></root>' AS XML) AS namexml FROM t )t
CROSS APPLY codxml.nodes('/root/r')m(n)
)a
INNER JOIN
(
SELECT ROW_NUMBER() OVER (PARTITION BY t.name ORDER BY name) AS seq,
p.q.value('.','varchar(50)') as name
FROM (SELECT name,CAST('<root><r>'+REPLACE(name,';','</r><r>')+'</r></root>' AS XML) AS namexml FROM t )t
CROSS APPLY namexml.nodes('/root/r')p(q)
)b
ON b.seq=a.seq
AND b.name>''
AND a.code>''



output
---------------------------
code name
T1 Pencil Box
T2 Eraser
T3 Board Marker
T4 Trimmer
T5 Ball Point

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

joshtheflame
Starting Member

5 Posts

Posted - 2013-03-07 : 01:00:13
Viskam many thanks..just one more thing..if more columns come aside cod,name for example amount,Ref is there easy method to add new fields and get the same result. Because cod and name are testing fields and I have more then 10 fields like this so how can I make it generic.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-07 : 01:04:01
nope...you've add a new join for each of the columns involved. If columns are not fixed, use dynamic sql.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

joshtheflame
Starting Member

5 Posts

Posted - 2013-03-07 : 01:14:42
Oh thanks for the guidance I did like this.


SELECT a.code,b.name,c.amount
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY t.cod ORDER BY cod) AS seq,
m.n.value('.','varchar(50)') as code
FROM (SELECT cod,CAST('<root><r>'+REPLACE(cod,';','</r><r>')+'</r></root>' AS XML) AS codxml,CAST('<root><r>'+REPLACE(name,';','</r><r>')+'</r></root>' AS XML) AS namexml FROM t )t
CROSS APPLY codxml.nodes('/root/r')m(n)
)a
INNER JOIN
(
SELECT ROW_NUMBER() OVER (PARTITION BY t.name ORDER BY name) AS seq,
p.q.value('.','varchar(50)') as name
FROM (SELECT name,CAST('<root><r>'+REPLACE(name,';','</r><r>')+'</r></root>' AS XML) AS namexml FROM t )t
CROSS APPLY namexml.nodes('/root/r')p(q)
)b
ON b.seq=a.seq
AND b.name>''
AND a.code>''
INNER JOIN
(
SELECT ROW_NUMBER() OVER (PARTITION BY t.amount ORDER BY amount) AS seq,
a.b.value('.','varchar(250)') as amount
FROM (SELECT amount,CAST('<root><r>'+REPLACE(amount,';','</r><r>')+'</r></root>' AS XML) AS amountxml FROM t )t
CROSS APPLY amountxml.nodes('/root/r')a(b)
)c
ON c.seq=b.seq
AND c.amount>''

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-07 : 04:02:56
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -