SQL query to split column data into rows

Question

I am having sql table in that I am having 2 fields as No and declaration

Code  Declaration
123   a1-2 nos, a2- 230 nos, a3 - 5nos

I need to display the declaration for that code as:

Code  Declaration 
123   a1 - 2nos 
123   a2 - 230nos 
123   a3 - 5nos

I need to split the column data to rows for that code.

Answer 1

For this type of data separation, I would suggest creating a split function:

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return 
end;

Then to use this in a query you can use an outer apply to join to your existing table:

select t1.code, s.items declaration
from yourtable t1
outer apply dbo.split(t1.declaration, ',') s

Which will produce the result:

| CODE |  DECLARATION |
-----------------------
|  123 |     a1-2 nos |
|  123 |  a2- 230 nos |
|  123 |    a3 - 5nos |

See SQL Fiddle with Demo

Or you can implement a CTE version similar to this:

;with cte (code, DeclarationItem, Declaration) as
(
  select Code,
    cast(left(Declaration, charindex(',',Declaration+',')-1) as varchar(50)) DeclarationItem,
         stuff(Declaration, 1, charindex(',',Declaration+','), '') Declaration
  from yourtable
  union all
  select code,
    cast(left(Declaration, charindex(',',Declaration+',')-1) as varchar(50)) DeclarationItem,
    stuff(Declaration, 1, charindex(',',Declaration+','), '') Declaration
  from cte
  where Declaration > ''
) 
select code, DeclarationItem
from cte

Answer 2

Try this....

declare @col1 varchar(100),@CurentSubString varchar(100)

create table #temp
(
    col1 varchar(50)
)

DECLARE CUR   CURSOR
FOR     SELECT  col1
        FROM    your_table
open    CUR

FETCH   next 
FROM    CUR
INTO    @col1

WHILE @@FETCH_STATUS = 0
BEGIN

    WHILE   CHARINDEX (@col1, ';') <> 0
    BEGIN
    SET @CurentSubString    =   SUBSTRING(@col1,1,CHARINDEX (@col1, ';'))
    SET @col1 = SUBSTRING(@col1,CHARINDEX (@col1, ';')+1,len(@col1))

    insert into #temp
    select @CurentSubString
END


IF CHARINDEX (@col1, ';') = 0 and isnull(@col1,'')!= '' 
BEGIN
    INSERT INTO #temp
    SELECT @col1
END


FETCH   next 
FROM    CUR
INTO    @col1

END


select  * 
From    #temp

CLOSE   CUR
DEALLOCATE CUR

Answer 3

Declare @t Table([Code] int, [Declaration] varchar(32));    
Insert Into @t([Code], [Declaration])
Values(123, 'a1-2 nos, a2- 230 nos, a3 - 5nos')

Select 
    x.[Code]
    ,t.Declaration  
    From
    (
        Select 
        *,
        Cast('<X>'+Replace(t.[Declaration],',','</X><X>')+'</X>' As XML) As record

        From @t t
    )x
    Cross Apply
    ( 
        Select fdata.D.value('.','varchar(50)') As Declaration 
        From x.record.nodes('X') As fdata(D)
    ) t

Few times back , I have blogged about the same Split Function in Sql Server using Set base approach

Also, please visit Erland Sommarskogblog who is maintaining the answer for the same since the last 15 years.