sql split row into three or more rows

Question

sql split row into three or more rows

compufreak

SSC Eights!

Points: 951
More actions
February 26, 2014 at 1:24 am

#288653

Hi everyone
I have a problem, I am trying to split a row that currently has information like this:
area: north,south,west
however I want the area column to display the information like this:
Area:
North
South
West
I have wrote a split function and it worked fine in other work I did...
however kind of having trouble with this one.
your guidance will for forever appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

compufreak (2/26/2014)
Hi everyone
I have a problem, I am trying to split a row that currently has information like this:
area: north,south,west
however I want the area column to display the information like this:
Area:
North
South
West
I have wrote a split function and it worked fine in other work I did...
however kind of having trouble with this one.
your guidance will for forever appreciated.

If the split function you wrote previously has a cursor, while loop or xml you should consider using a set based splitter instead. Take a look a the article in my signature about splitting strings. It will do this quite easily.

Then if at all possible see if you can normalize your data instead of multiple values in a single column.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Your final query would end up something like this.

create table #Something

(

Area varchar(50)

)

insert #Something

select 'north,south,west'

select split.Item as Area

from #Something s

cross apply dbo.DelimitedSplit8K(s.Area, ',') split

drop table #Something

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

compufreak SSC Eights! Points: 951 More actions · Answer 3

hi everyone

I have found a solution to the problem I had and I thought I should share it here with everyone...

DECLARE @tb_FElectricity AS TABLE (

row int identity(1,1),

cluster_desc varchar(225),

Dept_desc varchar(225),

day_month_year datetime,

headline varchar(max) ,

analysis varchar(50),

Category varchar(225));

DECLARE @tb_Totals AS TABLE (

_cluster_desc varchar(225),

_Dept_desc varchar(225),

_day_month_year datetime,

_headline varchar(max),

_analysis varchar(50),

_Category varchar(225) );

declare @rowcount int,@incr int

set @incr=1

INSERT INTO @tb_FElectricity (

cluster_desc,

Dept_desc,

day_month_year,

headline,

analysis,Category)

SELECT

cluster_desc, dept_desc,day_month_year,headline,analysis, Category

FROM

Source m

inner join Cluster_Dept cd on m.ID=cd.ID

inner join Clusters c on cd.Cluster_ID=c.Cluster_ID

inner join Department d on cd.Dept_ID=d.Dept_ID

Set @rowcount =(select COUNT(headline) from @tb_FElectricity)

while @incr<=@rowcount

begin

declare @charindex int =(select charindex(',',Category) from @tb_FElectricity where row=@incr)

while @charindex>0

Begin

insert into @tb_Totals (

_cluster_desc,

_Dept_desc,

_day_month_year,

_headline,

_analysis,

_Category )

select cluster_desc, Dept_desc,day_month_year,headline,analysis, case

when charindex(',',Category)>0

then SUBSTRING(Category,0,

charindex(',',Category))

Else Category

end as Category

from @tb_FElectricity t

where t.row=@incr

update @tb_FElectricity set Category=SUBSTRING(Category,

charindex(',',Category)+1,

LEN(Category)) where row=@incr

set @charindex=(select charindex(',',Category) from @tb_FElectricity where row=@incr)

End

if @charindex=0

begin

insert into @tb_Totals(

_cluster_desc,

_Dept_desc,

_day_month_year,

_headline,

_analysis,

_Category )

select cluster_desc, Dept_desc,day_month_year,headline,analysis,

case when charindex(',',Category)>0

then SUBSTRING(Category,0, charindex(',',Category))

else Category end as Category

from @tb_FElectricity t

where t.row=@incr

end

set @incr=@incr+1

end

select _Dept_desc as Department,

count(_headline) as numberofarticles,

_Category as Category,

_analysis as Analysis

FROM

@tb_Totals

WHERE

DateName( month , DateAdd( month , (MONTH(_day_month_year)) , 0 ) - 1 ) IN (SELECT Value FROM dbo.fn_REPORT_Split(@Month,',')) and

_day_month_year BETWEEN (Select FYEAR_S

FROM dbo.CalenderYear_new

WHERE dbo.CalenderYear_new.FYEAR = @FYEAR) AND

(Select FYEAR_E

FROM dbo.CalenderYear_new

WHERE dbo.CalenderYear_new.FYEAR = @FYEAR) and

_analysis <>'NULL'

and _analysis IN (SELECT Value FROM dbo.fn_REPORT_Split(@Analysis,','))

and _Cluster_Desc in (SELECT Value FROM dbo.fn_REPORT_Split(@Cluster,','))

group by _cluster_desc,_Dept_desc,_headline,_analysis,_Category

compufreak SSC Eights! Points: 951 More actions · Answer 4

the problem I have with this is the speed of the query...

any help with increasing the speed??

thanks again to everyone that assisted 🙂

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

compufreak (3/4/2014)
the problem I have with this is the speed of the query...
any help with increasing the speed??
thanks again to everyone that assisted 🙂

Go back and read Sean's post. The fix for speed is there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)