how to write T-sql script to parse a multi valued column?

  • 1.  I have a table simplified as below:

    Col 1 type int: userID

    Col 2 type ntext

    So a sample data is:

    Col 1: 281

    Col 2: 2001, 4888, 24, 367

    Col 2 is multi valued with 0 or any number of integer IDs separated by commas.

    2.  I have another table that is the parent of the codes in col 2 of the above table.So this table will show:

    ID     Description

    24     Project1

    367   Mama Mia

    2001  Papa John

    4888  Whatever  

    3.  What I need is to construct a temp table from the 2 tables above.  This temp table has this layout:

    Col 1:  UserID

    Col 2:  all relevant description(s)

    sample data:

    Col 1                          Col 2

    --------------------------------------------------------------------

    281                            Project1, MamaMia, PapaJohn, Whatever

    309                            <NULL>

    310                            SomeDescription1, SomeDescription2

    Can I create this table with T-SQL script?  If yes, how?  Thanks

     

     

     

     

  • Yes. Here you go.

    create table table1

    (

    UserIDint,

    Datantext

    )

    insert into table1

    select281, '2001, 4888, 24, 367'union all

    select309, NULL

    create table table2

    (

    IDint,

    Descriptionsvarchar(100)

    )

    insert into table2

    select 24, 'Project1'union all

    select 367, 'Mama Mia'union all

    select 2001, 'Papa John'union all

    select 4888, 'Whatever'

    create table #temp

    (

    UserIDint,

    Descriptionsvarchar(500)

    )

    CREATE function dbo.csv2table(@Str varchar(7000))

    returns @t table (value varchar(100))

    as

    begin

    declare @i int;

    declare @C varchar(100);

    set @STR = @STR + ','

    set @i = 1;

    set @C = '';

    while @i <= len(@Str)

    begin

    if substring(@Str,@i,1) = ','

    begin

    insert into @t

    values (rtrim(ltrim(@c)))

    set @C = ''

    end

    else

    set @C = @C + substring(@Str,@i,1)

    set @i = @i +1

    end

    return

    end

    create function dbo.csv2desc(@str varchar(7000))

    returns varchar(7000)

    as

    begin

    declare

    @descvarchar(7000)

    select@desc = isnull(@desc, '') + t2.Descriptions + ', '

    fromdbo.csv2table(@str) t1 inner join table2 t2

    ont1.value= t2.ID

    return @desc

    end

    select *, dbo.csv2desc(convert(varchar(7000), Data))

    from table1

  • Thank you, KH for sharing your knowledge and script code.  That works like a charm.

  • KH,

    What will have to change when the table in step 1 is laid out as below:

    Col 1 (type Int) :  UserID

    Col 2-12(type ntext):  string of integers.

    So a sample data will look as below:

    Col 1:  281

    Col 2:  23, 25

    Col 3:  null

    Col 4:  45, 288, 307, 4000

    Col 5:  14

    Col 6:  null

    Col 7:  12,15

    Col 8:  10, 11, 23, 24

    Col 9: etc...

    Somehow I will have to interrogate all col2-12 to retrieve all the comma separated values for a given user ID.

    Thank you much in advance.

     

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

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