none
Split 1 row into multiple rows based on column value

    Question

  • Hi,

    I have the following challenge.

     

    I need to split a single row into mulitple rows based on the number of values in a column. Say this is my input set (2 rows):

     

    Code    Group_Filter

    KPLIOT 14500|14550|14600|14650|14750|14800

    KPLJCO 12510|12520|12530|12550|12610

     

    The values in column Group_Filter are seperated from eachother using pipe-symbols. I want to have these values in seperated rows. So this is what I would like to have as a result:

    Code Filter

    KPLIOT 14500

    KPLIOT 14550

    KPLIOT 14600

    KPLIOT 14650

    KPLIOT 14750

    KPLIOT 14800

    KPLJCO 12510

    KPLJCO 12520

    KPLJCO 12530

    KPLJCO 12550

    KPLJCO 12610

    Any suggestions on how to achieve this (probably using a cursor ?)

    Kind regards,

     

    Cees

     

     

     

     


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Friday, July 16, 2010 2:08 PM

Answers

  • For these requirements I use a Split-Function:

    declare @code varchar(10)
    declare @filter varchar(250)

    declare mycur cursor for select Code, Group_filter from table
    open mycur

    fetch next from mycur into @code, @filter

    while @@fetch_status = 0

    begin

    insert into new_table (code, group_filter)
    select @code, value from dbo.Fn_Split(@filter,'|')

    fetch next from mycur into @code, @filter

    end

    close mycur
    deallocate mycur

    USE [Test]
    GO

    /****** Object:  UserDefinedFunction [dbo].[fn_Split]    Script Date: 07/16/2010 16:16:41 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    --LISTING 1: The Fn_Split() Function
    --http://www.winnetmag.com/SQLServer/Article/ArticleID/21071/21071.html

    CREATE FUNCTION [dbo].[fn_Split](@sText varchar(8000), @sDelim varchar(20) = ' ')
    RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
    AS
    BEGIN
    DECLARE @idx smallint,
     @value varchar(8000),
     @bcontinue bit,
     @iStrike smallint,
     @iDelimlength tinyint

    IF @sDelim = 'Space'
     BEGIN
     SET @sDelim = ' '
     END

    SET @idx = 0
    SET @sText = LTrim(RTrim(@sText))
    SET @iDelimlength = DATALENGTH(@sDelim)
    SET @bcontinue = 1

    IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
     BEGIN
     WHILE @bcontinue = 1
      BEGIN

    --If you can find the delimiter in the text, retrieve the first element and
    --insert it with its index into the return table.
     
      IF CHARINDEX(@sDelim, @sText)>0
       BEGIN
       SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
        BEGIN
        INSERT @retArray (idx, value)
        VALUES (@idx, @value)
        END
       
    --Trim the element and its delimiter from the front of the string.
       --Increment the index and loop.
    SET @iStrike = DATALENGTH(@value) + @iDelimlength
       SET @idx = @idx + 1
       SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
      
       END
      ELSE
       BEGIN
    --If you can’t find the delimiter in the text, @sText is the last value in
    --@retArray.
     SET @value = @sText
        BEGIN
        INSERT @retArray (idx, value)
        VALUES (@idx, @value)
        END
       --Exit the WHILE loop.
    SET @bcontinue = 0
       END
      END
     END
    ELSE
     BEGIN
     WHILE @bcontinue=1
      BEGIN
      --If the delimiter is an empty string, check for remaining text
      --instead of a delimiter. Insert the first character into the
      --retArray table. Trim the character from the front of the string.
    --Increment the index and loop.
      IF DATALENGTH(@sText)>1
       BEGIN
       SET @value = SUBSTRING(@sText,1,1)
        BEGIN
        INSERT @retArray (idx, value)
        VALUES (@idx, @value)
        END
       SET @idx = @idx+1
       SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
       
       END
      ELSE
       BEGIN
       --One character remains.
       --Insert the character, and exit the WHILE loop.
       INSERT @retArray (idx, value)
       VALUES (@idx, @sText)
       SET @bcontinue = 0 
       END
     END

    END

    RETURN
    END


    GO

    Friday, July 16, 2010 2:22 PM

All replies

  • For these requirements I use a Split-Function:

    declare @code varchar(10)
    declare @filter varchar(250)

    declare mycur cursor for select Code, Group_filter from table
    open mycur

    fetch next from mycur into @code, @filter

    while @@fetch_status = 0

    begin

    insert into new_table (code, group_filter)
    select @code, value from dbo.Fn_Split(@filter,'|')

    fetch next from mycur into @code, @filter

    end

    close mycur
    deallocate mycur

    USE [Test]
    GO

    /****** Object:  UserDefinedFunction [dbo].[fn_Split]    Script Date: 07/16/2010 16:16:41 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    --LISTING 1: The Fn_Split() Function
    --http://www.winnetmag.com/SQLServer/Article/ArticleID/21071/21071.html

    CREATE FUNCTION [dbo].[fn_Split](@sText varchar(8000), @sDelim varchar(20) = ' ')
    RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
    AS
    BEGIN
    DECLARE @idx smallint,
     @value varchar(8000),
     @bcontinue bit,
     @iStrike smallint,
     @iDelimlength tinyint

    IF @sDelim = 'Space'
     BEGIN
     SET @sDelim = ' '
     END

    SET @idx = 0
    SET @sText = LTrim(RTrim(@sText))
    SET @iDelimlength = DATALENGTH(@sDelim)
    SET @bcontinue = 1

    IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
     BEGIN
     WHILE @bcontinue = 1
      BEGIN

    --If you can find the delimiter in the text, retrieve the first element and
    --insert it with its index into the return table.
     
      IF CHARINDEX(@sDelim, @sText)>0
       BEGIN
       SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
        BEGIN
        INSERT @retArray (idx, value)
        VALUES (@idx, @value)
        END
       
    --Trim the element and its delimiter from the front of the string.
       --Increment the index and loop.
    SET @iStrike = DATALENGTH(@value) + @iDelimlength
       SET @idx = @idx + 1
       SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
      
       END
      ELSE
       BEGIN
    --If you can’t find the delimiter in the text, @sText is the last value in
    --@retArray.
     SET @value = @sText
        BEGIN
        INSERT @retArray (idx, value)
        VALUES (@idx, @value)
        END
       --Exit the WHILE loop.
    SET @bcontinue = 0
       END
      END
     END
    ELSE
     BEGIN
     WHILE @bcontinue=1
      BEGIN
      --If the delimiter is an empty string, check for remaining text
      --instead of a delimiter. Insert the first character into the
      --retArray table. Trim the character from the front of the string.
    --Increment the index and loop.
      IF DATALENGTH(@sText)>1
       BEGIN
       SET @value = SUBSTRING(@sText,1,1)
        BEGIN
        INSERT @retArray (idx, value)
        VALUES (@idx, @value)
        END
       SET @idx = @idx+1
       SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
       
       END
      ELSE
       BEGIN
       --One character remains.
       --Insert the character, and exit the WHILE loop.
       INSERT @retArray (idx, value)
       VALUES (@idx, @sText)
       SET @bcontinue = 0 
       END
     END

    END

    RETURN
    END


    GO

    Friday, July 16, 2010 2:22 PM
  • There are many different splitting functions available. Take a look at http://www.sommarskog.se/arrays-in-sql.html and pick up the version that suits your best.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, July 16, 2010 2:34 PM
    Moderator
  • Hi Christa,

    Thnx a lot for your reply. You saved my day and made my weekend. Only some more points left to get your 2nd medal.

    I just wondered, which Microsoft partner is your employer ? 

     

    Kind regards,

    Cees

     

     

     


    Please remember to mark the replies as answers or at least vote helpfull if they help and unmark them if they provide no help.
    Friday, July 16, 2010 3:02 PM