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
DCyou have my sincere condolences

i would do this with application programming, not sql