| Author |
 Topic  |
|
|
joshtheflame
Starting Member
4 Posts |
 Posted - 03/07/2013 : 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
India
48076 Posts |
Posted - 03/07/2013 : 00:46:29
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
joshtheflame
Starting Member
4 Posts |
Posted - 03/07/2013 : 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 03/07/2013 : 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/
|
|
|
|
joshtheflame
Starting Member
4 Posts |
Posted - 03/07/2013 : 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>''
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 03/07/2013 : 04:02:56
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
How to Convert Semi colon Separated Values into Co
