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.

Taryn · Accepted Answer · 2012-11-24T13:11:13.710

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

Happy to help. If this answer is helpful be sure to accept via the checkmark on the left. It will help future visitors and you will get reputation for accepting! :) — Taryn, Nov 24 '12 at 04:18
because of your help I am having a result as Code Declaration 123 a1 - 2nos 123 a2 - 230nos 123 a3 - 5nos 123 a1 - 100nos 123 a3 - 6nos is it possible to sum the duplicates and i need the output to be displayed like this Code Declaration 123 a1 - 102nos 123 a2 - 230nos 123 a3 - 11nos — Affan, Nov 24 '12 at 05:53
@Affan I answered your other question with a solution on how to total the values. — Taryn, Nov 24 '12 at 13:13
please guide me in this post...http://stackoverflow.com/questions/15917099/subquery-returned-more-than-1-value-in-insert-statement — Affan, Apr 10 '13 at 11:49

Niladri Biswas · Answer 2 · 2012-11-23T14:32:47.833

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.

score 0 · Answer 3 · edited Apr 13 '15 at 09:05

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

SQL query to split column data into rows

3 Answers3

Linked

Related