SQL Server 2014 - How to split a string with commas into multiple rows?

Question

I have written a query which returns a table (tb1) with rows of data. One column shows a list of queues an analyst can work on and the other columns show how many analysts are available to work on those queues and how many are away.

I now need to wrap this query in another so it returns the row, broken down into multiple rows each showing how many analysts are avail/away per queue.

table 1

UPDATE - I have created this post when there are similar ones out there because the Queue column is the key, which needs to be separated into individual rows each with the same data from the other (avail/away) columns when it was just one row. To add complexity there can be some rows with more/less queue names that need grouping. example is updated.

http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows — Ola Ekdahl, Aug 05 '16 at 16:25

John Cappelletti · Accepted Answer · 2016-08-05T16:50:10.007

Here is my udf. There are many variations out there. What I like about this one is you get the sequence number

EDIT:

Sorry, I didn't see your image. But with the help of a Cross Apply

Declare @YourTable table (id int,SomeField varchar(50))
Insert into @YourTable values
(1,'Smith,John'),
(2,'Doe,Jane')

Select A.ID
      ,YourNewFieldName=B.Key_Value
  From @YourTable A
  Cross Apply (Select * from [dbo].[udf-Str-Parse](A.SomeField,',')) B

Returns

ID  YourNewFieldName
1   Smith
1   John
2   Doe
2   Jane

The UDF

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--       Select * from [dbo].[udf-Str-Parse]('id26,id46|id658,id967','|')
--       Select * from [dbo].[udf-Str-Parse]('hello world. It. is. . raining.today','.')

Returns @ReturnTable Table (Key_PS int IDENTITY(1,1), Key_Value varchar(max))
As
Begin
   Declare @XML xml;Set @XML = Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML)
   Insert Into @ReturnTable Select Key_Value = ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String)
   Return 
End

Sample Return

Key_PS  Key_Value
1       Dog
2       Cat
3       House
4       Car

Will check out your function but getting stuck with the queue names as the ID. I have updated my main post as it was too similar to others. — Talbius, Aug 08 '16 at 09:09

SQL Server 2014 - How to split a string with commas into multiple rows?

1 Answers1