SQL procedure to separate the parsed column values.

Question

SQL procedure to separate the parsed column values.

g_rose42

SSC Enthusiast

Points: 134
More actions
September 13, 2014 at 9:30 am

#296103

I have facing some difficulties to build a SQL Query for my new requirement below is my table I have to list the items and paramlist in the gridview
i have attached image for my table and o/p.
in the table paramlist column contains value like below
declare @vari varchar(max)='param=1;param=2;param=3;param=4;...param=20;param=21;'
Table1
itemname paramlist
-------------------------
item1 @vari
item2 @vari
Note: Param length might be vary based on each item (ex:item1 can have param upto 20 and item2 30)
o/p
itemparam1param2param3param4…
----------------------------------------------------------
item11 2 3 4…
item211 22 null 44…

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

g_rose42 (9/13/2014)
I have facing some difficulties to build a SQL Query for my new requirement below is my table I have to list the items and paramlist in the gridview
i have attached image for my table and o/p.
in the table paramlist column contains value like below
declare @vari varchar(max)='param=1;param=2;param=3;param=4;...param=20;param=21;'
Table1
itemname paramlist
-------------------------
item1 @vari
item2 @vari
Note: Param length might be vary based on each item (ex:item1 can have param upto 20 and item2 30)
o/p
itemparam1param2param3param4…
----------------------------------------------------------
item11 2 3 4…
item211 22 null 44…

Hi and welcome to the forum.

There are multiple options for doing this, normally the DelimitedSplit8K [/url]function would be the first choice.

😎

g_rose42 SSC Enthusiast Points: 134 More actions · Answer 2

Thanks for the reply. But I think that split function gives the values by row. i need it by column by.

Even though if i convert into row by then i can use pivot but in the pivot i have to mention column names like param1,param2,... here column name may vary by the each item so cant use static.

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 3

g_rose42 (9/14/2014)
Thanks for the reply. But I think that split function gives the values by row. i need it by column by.
Even though if i convert into row by then i can use pivot but in the pivot i have to mention column names like param1,param2,... here column name may vary by the each item so cant use static.

If you need to dynamically assign the column names, there is only one option and that is to use dynamic sql. My first thought is to use the DelimitedSplit8K to parse the parameters, cross tab the results, then in dynamic sql create a table with the right column names, insert the cross tab'd results and select it from there.

😎