Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2007
    Posts
    2

    Unanswered: Column Splitting

    Hi Everyone,

    I've been given the painstaking project of splitting a single column into multiple columns and rows. I have a solution set up in which I will be posting further down the post but I want to see if there is a much more efficient solution to this.

    sample data:
    create table tbl_list
    (pk_int_itmid int(5) Primary Key,
    vchar_desk vchar(300));

    create table tbl_test1
    (fk_int_itmid int(5) references tbl_list(pk_int_itmid),
    vchar_itm varchar(60));

    insert into tbl_list values
    (1, 'this item');

    insert into tbl_list values
    (2, 'that item');

    insert into tbl_list values
    (3, 'those items');

    insert into tbl_test1 values
    (1, 'A, B - C, D, E - F, G, H - I');

    insert into tbl_test1 values
    (2, 'J, K - L, M, N - O');

    insert into tbl_test1 values
    (3, 'P, Q - R');

    into this table:
    create table tbl_output
    (fk_int_itmid int(5) references tbl_list(pk_int_itmid),
    vchar_itmA varchar(60),
    vchar_itmB varchar(60),
    vchar_itmC varchar(60));

    Output in comma delimited form:
    '1', 'A', 'B', 'C'
    '1', 'D', 'E', 'F'
    '1', 'G', 'H', 'I'
    '2', 'J', 'K', 'L'
    '2', 'M', 'N', 'O'
    '3', 'P', 'Q', 'R'

    my current solution:
    create view vw_itm_a as
    select fk_int_itmid,
    substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,
    substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,
    substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmC
    from tbl_test1
    where charindex(',',vchar_itm) >1
    Go

    create view vw_itm_b as
    select fk_int_itmid,
    substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,
    substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,
    substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmC
    from vw_itm_a
    where charindex(',',vchar_itmC) >1;
    Go

    create view vw_itm_c as
    select fk_int_itmid,
    substring(vchar_itmC, 0, charindex('-',vchar_itmC)) as vchar_itmA,
    substring(vchar_itmC, charindex('-',vchar_itmC)+1 , charindex(',',vchar_itmC)-charindex('-',vchar_itmC)) as vchar_itmB,
    substring(vchar_itmC, charindex(',',vchar_itmC)+1) as vchar_itmC
    from vw_itm_b
    where charindex(',',vchar_itmC) >1;
    Go;

    create view vw_itm_d as
    select fk_int_itmid, vchar_itmA, vchar_itmB,
    substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmC
    from vw_itm_a ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmid
    Go;

    create view vw_itm_e as
    select fk_int_itmid, vchar_itmA, vchar_itmB,
    substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmC
    from vw_itm_c ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmid
    Go;

    create view vw_itm as
    select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
    from vw_itm_a
    where fk_int_itmid not in (select fk_int_itmid from vw_itm_b)
    union
    select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
    from vw_itm_d
    union
    select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
    from vw_itm_b
    where fk_int_itmid not in (select fk_int_itmid from vw_itm_c)
    union
    select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
    from vw_itm_e
    union
    select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
    from vw_itm_c
    Go;

    select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
    into tbl_output
    from vw_itm

    Is there a much more efficient manner of handling this column splitting?

    Thanks
    DC

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have my sincere condolences

    i would do this with application programming, not sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •