SQL Server expand comma delimited field into rows
Turn this
| fund_id | subfunds |
|---|---|
| 123 | AA,BB,CC |
into this
| fund_id | subfunds |
|---|---|
| 123 | AA |
| 123 | BB |
| 123 | CC |
and now you are a SQL hero!
The trick is the UNION with recursive select from exploded_subfunds.
select
fund_id,
subfunds
from
sf_foo;
with exploded_subfunds(fund_id, subfund, subfunds) as (
select
fund_id,
convert(varchar(100),LEFT(subfunds, CHARINDEX(',',subfunds+',')-1)),
convert(varchar(100),STUFF(subfunds, 1, CHARINDEX(',',subfunds+','), ''))
from
sf_foo
union all
select
fund_id,
convert(varchar(100),LEFT(subfunds, CHARINDEX(',',subfunds+',')-1)),
convert(varchar(100),STUFF(subfunds, 1, CHARINDEX(',',subfunds+','), ''))
from
exploded_subfunds
where
subfunds > ''
)
select
fund_id,
subfund
from
exploded_subfunds;
select LEFT('AA,BB,CC', CHARINDEX(',','AA,BB,CC'+',')-1);
-- AA
select STUFF('AA,BB,CC', 1, CHARINDEX(',','AA,BB,CC'+','), '');
-- BB,CC
