CODEWITHSUNDEEP
sqlsql-serverpivot
ThomasRones
ThomasRones

Reputation: 683

Sql server Pivot and create multiple records from one field and split other field

What my data looks like:

Name:   Animals(One-Column):
Bill    Dog, Cat, Horse
Bob     Dog, Giraffe, Snake , Horse

What I need to transform it to:

Name:   Animal:
Bill    Dog
Bill    Cat
Bill    Horse
Bob     Dog
Bob     Giraffe
Bob     Snake
Bob     Horse

I am unsure of the steps to take, there are anywhere from 0 - 100 values in the actual csv field.

You will notice that the csv's are not consistent (Horse is word 3 for Bill but word 4 for Bob), so I need a solution like:

1st Name  1st Word
1st Name  2nd Word
2nd Name  1st Word
2nd Name  2nd Word
2nd Name  3rd Word
...

Upvotes: 1

Views: 78

Answers (2)

Chanukya
Chanukya

Reputation: 5883

create function [dbo].[udf_splitstring] (@tokens    varchar(max),
                                         @delimiter varchar(5))
returns @split table (
  token varchar(200) not null )
as
  begin
      declare @list xml

      select @list = cast('<a>'
                          + replace(@tokens, @delimiter, '</a><a>')
                          + '</a>' as xml)

      insert into @split
                  (token)
      select ltrim(t.value('.', 'varchar(200)')) as data
      from   @list.nodes('/a') as x(t)

      return
  end



  CREATE TABLE #TABLE1
    ([NAME] VARCHAR(4), [ANIMALS] VARCHAR(27))
;

INSERT INTO #TABLE1
    ([NAME], [ANIMALS])
VALUES
    ('BILL', 'DOG, CAT, HORSE'),
    ('BOB', 'DOG, GIRAFFE, SNAKE , HORSE')
;
SELECT * FROM #TABLE1

SELECT NAME ,TOKEN FROM #TABLE1 AA
CROSS APPLY (SELECT * FROM UDF_SPLITSTRING(ANIMALS,','))A

output

name    token
Bill    Dog
Bill    Cat
Bill    Horse
Bob     Dog
Bob    Giraffe
Bob    Snake 
Bob    Horse

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269583

One method is a recursive CTE:

with cte as (
      select name, animals, 1 as lev,
             left(animals, charindex(',', animals + ',')) as animal
      from t
      union all
      select name, animals, lev + 1,
             left(animals, charindex(',', animals + ',')) as animal
      from cte
      where animals <> ''
     )
select name, animal, lev
from cte;

You may have to set the max recursion option if you actually have 100 items in the list.

Upvotes: 0

Related Questions