SQL Server 2005: How to split a stacked column with unknown number of splits

Question

I am having more issues with splitting stacked columns, and would love some help to complete this last part. I was trying to apply another solution I had, but with no luck.

DB Table:

ID INT,
SN varchar(100),
Types varchar(1000)

Sample:

ID     SN    Types
1      123   ABC,XYZ,TEST
2      234   RJK,CDF,TTT,UMB,UVX
3      345   OID,XYZ

Desired output:

ID     SN    Types
1      123   ABC    
1      123   XYZ
1      123   TEST
....

Yeah, trust me I am working with the worst design DB ever. They use varchar for datetime fields, but it is what it is, so I am making the best of it. My SQL is getting better by the day. — Michael, Jun 22 '11 at 19:58
Just the first of MANY results when searching for "sql split comma" on here: http://stackoverflow.com/questions/951401/sql-2005-split-comma-separated-column-on-delimiter — Tom H, Jun 22 '11 at 20:02
@Michael It's excellent that you're getting something good out of something bad. Keep up the learning! — Adriano Carneiro, Jun 22 '11 at 20:06
I've answered this question many times... check this out: [Arrays and Lists in SQL Server 2005 and Beyond](http://www.sommarskog.se/arrays-in-sql-2005.html) it is the most comprehensive article on the subject I've ever come across. — KM., Jun 22 '11 at 20:16

DForck42 · Answer 1 · 2011-06-22T20:32:48.607

here's a cte i have to break up a delimited string

declare @table table (ID int identity(1,1), String varchar(max))
declare @delim varchar(max)

insert into @table values ('abc,def')
insert into @table values ('ghij,klmn,opqrst')

set @delim=','


;with c as
(
    select 
        ID, 
        --String,
        CHARINDEX(@delim,String,1) as Pos,
        case when CHARINDEX(@delim,String,1)>0 then SUBSTRING(String,1,CHARINDEX(@delim,String,1)-1) else String end as value,
        case when CHARINDEX(@delim,String,1)>0 then SUBSTRING(String,CHARINDEX(@delim,String,1)+1,LEN(String)-CHARINDEX(@delim,String,1)) else '' end as String
    from @table

    union all
    select
        ID,
        CHARINDEX(@delim,String,1) as Pos,
        case when CHARINDEX(@delim,String,1)>0 then SUBSTRING(String,1,CHARINDEX(@delim,String,1)-1) else String end as Value,
        case when CHARINDEX(@delim,String,1)>0 then SUBSTRING(String,CHARINDEX(@delim,String,1)+1,LEN(String)-CHARINDEX(@delim,String,1)) else '' end as String
    from c
    where LEN(String)>0
)

select ID, Value from c

Wow, I really need to learn more T-SQL, so let me ask the dumb question, how would I query the three columns, and call this cte to present the data? — Michael, Jun 22 '11 at 20:16

Mikael Eriksson · Answer 2 · 2011-06-22T20:52:16.157

2

declare @T table(ID int, SN varchar(100), Types varchar(1000))

insert into @T
select 1, 123, 'ABC,XYZ,TEST' union all
select 2, 234, 'RJK,CDF,TTT,UMB,UVX' union all
select 4, 234, 'XXX' union all
select 3, 345, 'OID,XYZ'

;with cte(ID, SN, Types, Rest) as
(
  select ID,
         SN,
         cast(substring(Types+',', 1, charindex(',', Types+',')-1) as varchar(100)),
         stuff(Types, 1, charindex(',', Types), '')+','
  from @T
  where len(Types) > 0
  union all
  select ID,
         SN,
         cast(substring(Rest, 1, charindex(',', Rest)-1) as varchar(100)),
         stuff(Rest, 1, charindex(',', Rest), '')
  from cte
  where len(Rest) > 0
)
select ID, SN, Types
from cte
order by ID

I use a recursive CTE to split the string. The third column Types is populated with the first word in the Types column of @T. Stuff will then remove the first word and populate the Rest column that then will contain everything but the first word. After UNION ALL is the recursive part that basically do the exact same thing but it uses the CTE as a source and it uses the rest column to pick the first word. The first word of the rest column is removed with stuff and then ..... well it is recursive so I think I will stop here with the explanation. The recursive part will end when there are no more words left where len(Rest) > 0.

edited Jun 22 '11 at 20:52

answered Jun 22 '11 at 20:26

Mikael Eriksson

136,425
22
210
281

wow you are awesome Mikael, its a great start for me to doing what I need. If you can send private chats here, please do would love to pick you brain from time to time. – Michael Jun 22 '11 at 20:32
So one follow up question, what is the idea behind the Rest being stuffed in the union all, I am a little clear on why the union all is needed and where the data with Rest is going? – Michael Jun 22 '11 at 20:33
OK lied two follow ups, the Types column has nulls which seems to produce and error but I would believe that the len(Types) > 0 should cover that, am I wrong? – Michael Jun 22 '11 at 20:38
@Michael - Do you get error on null value in Types? `len(Types) > 0` should take care of that. I will try to ad a bit of explanation what the query actually do to the answer. – Mikael Eriksson Jun 22 '11 at 20:42
I get the error "Invalid length parameter passed to the substring function. IsNull maybe? – Michael Jun 22 '11 at 20:47
@Michale, ahh, you probably have a value that doesn't have a comma! – DForck42 Jun 22 '11 at 20:49
@DForck42 - Thats it, thanks. @Michael - Updated answer to take care of `Types` with only one word. (no comma) – Mikael Eriksson Jun 22 '11 at 20:54
makes sense. let me investigate. – Michael Jun 22 '11 at 21:59
That was it, had to add in case it was not there, thanks a lot. – Michael Jun 23 '11 at 12:23

therealmitchconnors · Answer 3 · 2011-06-22T20:11:54.153

You will need to use a cursor and a while statement as far as I can tell... Some of these indexes may be off by one, but I think this should get you there...

DECLARE MY_CURSOR Cursor 
FOR
SELECT ID, SN, Types
FROM Tbl1
Open My_Cursor 
DECLARE @ID int, @SN varchar(100), @types varchar(1000)
Fetch NEXT FROM MY_Cursor INTO @ID, @SN, @types
While (@@FETCH_STATUS <> -1)
BEGIN
  DECLARE @Pos int
  WHILE @Pos < LEN(@types)
  BEGIN
    DECLARE @type varchar(25)
    DECLARE @nextpos int
    set @nextpos = CHARINDEX(@types, ',', @pos)
    SET @type = SUBSTRING(@types, @pos, @nextpos-@pos)
    INSERT INTO tbl2 (ID, SN, type) VALUES (@ID, @SN, @Type)
    SET @Pos = @nextpos+1
  END


FETCH NEXT FROMMY_CURSOR INTO @VAR1Number, @VAR2DateTime ,@VarLongText 
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

SQL Server 2005: How to split a stacked column with unknown number of splits

3 Answers3