February 26, 2014 at 1:24 am
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.
February 26, 2014 at 7:31 am
compufreak (2/26/2014)
Hi everyoneI 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/
February 26, 2014 at 7:34 am
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/
March 4, 2014 at 11:18 pm
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
March 4, 2014 at 11:22 pm
the problem I have with this is the speed of the query...
any help with increasing the speed??
thanks again to everyone that assisted 🙂
March 5, 2014 at 12:09 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply