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.
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 Amountand values will be in this formatItemCod 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 gotINSERT 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)') nameINTO #tmpTable FROM tinsert INTO #tmpTableSELECT 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)') nameFROM t;select * from #tmpTable scripts to create tableCREATE 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 nameT1 Pencil BoxT2 EraserT3 Board MarkerT4 TrimmerT5 Ball Point[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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>'' |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-07 : 04:02:56
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|