SQL Server 2008 Splitting string variable number of token per line

Question

I have a fairly simple requirement -I have a table with the following (relevant) structure.

with cte as(
select 1 id,'AA,AB,AC,AD' names union all
select 2,'BA,BB' union all
select 3,'CA,CB,CC,CD,CE' union all
select 4,'DA,DB,DC'
)

i would like to create a select statement which will split each "names" column into multiple rows.

For example the first row should produce

1,'AA'
1,'AB'
1,'AC'
1,'AD'

Can we do it using only SQL. This is failry easy to do in Oracle.

Answer 1

You can create a split function that returns a table, then select from that table.

/***************************************************************************
 **
 ** Function: split
 ** In: @ipRowData - The delimited list of items to split.
 ** In: @ipSplitOn - The delimiter which separates the items in @rowData.
 ** Returns: A table object containing the split items. The table object
 **    will have an ID and Data column, where ID is the number of the item
 **    in the original list and Data is the value of the item.
 **
 ** Description:
 **    Splits a delimited set of items and returns them
 **    as a table object.
 ***************************************************************************/
CREATE FUNCTION [dbo].[split]
(
   @ipRowData NVARCHAR(4000),
   @ipSplitOn NVARCHAR(5)
)
RETURNS @rtnValue table
(
   ID INT identity(1,1),
   Data NVARCHAR(100)
)
AS
BEGIN
   DECLARE
      @cnt INT
      Set @cnt = 1

   WHILE (Charindex(@ipSplitOn,@ipRowData)>0)
   BEGIN
      INSERT INTO @rtnValue
                ( data )
                  SELECT Data = ltrim(rtrim(Substring(@ipRowData,1,Charindex(@ipSplitOn,@ipRowData)-1)))
      SET @ipRowData = Substring(@ipRowData,Charindex(@ipSplitOn,@ipRowData)+1,len(@ipRowData))
      SET @cnt = @cnt + 1
   END

   INSERT INTO @rtnValue (data)
   SELECT DATA = ltrim(rtrim(@ipRowData))

   RETURN
END

GO

Sample Usage:

select 1,data from [dbo].split('AA,AB,AC,AD', ',');

Output:

(No column name)   data
1                  AA
1                  AB
1                  AC
1                  AD

Answer 2

You can do it in one query with no custom defined functions if you leverage XML:

WITH cte AS( /*your data*/
    SELECT 1 id,'AA,AB,AC,AD' names UNION ALL
    SELECT 2,'BA,BB' UNION ALL
    SELECT 3,'CA,CB,CC,CD,CE' UNION ALL
    SELECT 4,'DA,DB,DC'
)
, xmlData AS ( /*make into xml*/
    SELECT id, cast('<root><x>'+replace(names,',','</x><x>')+'</x></root>' as xml) AS theXML
    FROM cte
)
SELECT id, x.value('.','varchar(100)')  /*split up*/
FROM xmlData
CROSS APPLY  xmlData.theXML.nodes('//x') AS func(x)