February 21, 2013 at 9:26 pm
Ok this script works for me but wanted the experts opinion on a better/faster way or more efficient way..
Scenario:
Have data with products and settings.
ID is sequence order.
want to concatenate the individual settings into a string for the product.
i.e.
Prod Setting
A A1
A B1
A C2
I want:
Prod Setting
A A1-B1-C2
The script below takes about a minute to run on my laptop...
returns about 32K records...
Help is much appreciated.. 😀
use tempdb
go
if object_id('#Table') is null
create table #Table(ID int, Val varchar(50),Prod varchar(50));
truncate table #Table
--Just Sample Data==============================
insert into #Table
values(1, 'A1', 'EZ'),(2,'B2','EZ'),(3,'C2','EZ'),(4,'D2','EZ')
,(1, 'A1','EX'),(2,'B2','EX'),(3,'C2','EX'),(4,'D2','EX'),(5,'E3','EX')
,(1, 'A1','ED'),(2,'B2','ED'),(3,'C2','ED'),(4,'D2','ED'),(5,'E3','ED'),(6,'F3','ED');
--This is the actual work script
with t(ID, Val, Prod, Pos) as (select a.ID, a.Val, a.Prod, 1
from #Table a
where id = 1
union all
select b.ID, b.Val, cast(b.Prod + cast(Pos as varchar) as varchar(50)), Pos + 1
from #Table b
, t c
where b.Prod = left(c.Prod,2)
)
insert into #Table(ID, Val, Prod)
select top 192000 ID, Val, Prod from t
where len(Prod) > 2
option (maxrecursion 32000)
--End of populating Sample Data
--Data in sample table
select * from #Table;
with E1(ID,Prod,Val,MaxID, Pos) as (select ID, Prod, Val, 0, 1
from #Table
where ID = 1
union all
select t.ID, t.Prod, cast(e1.Val + '-' + t.Val as varchar(50)),max(t.ID)over(partition by t.Prod), Pos + 1
from #Table t
inner join E1
on t.ID > e1.ID
and t.prod = e1.prod
)
--Desired output
select *
from e1
where MaxID = Pos
Drop Table #Table
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
March 2, 2013 at 6:04 pm
Looking at this but I am slightly confused as to what it is you are actually attempting to accomplish.
Based on your description what you seem to be looking for is more like this:
-- Initial table with data:
if exists(select 1 from sys.objects where object_id = object_id('tempdb..#Table'))
drop table #Table;
create table #Table(ID int, Setting varchar(50),Prod varchar(50));
--Just Sample Data==============================
insert into #Table
values(1, 'A1', 'EZ'),(2,'B2','EZ'),(3,'C2','EZ'),(4,'D2','EZ')
,(1, 'A1','EX'),(2,'B2','EX'),(3,'C2','EX'),(4,'D2','EX'),(5,'E3','EX')
,(1, 'A1','ED'),(2,'B2','ED'),(3,'C2','ED'),(4,'D2','ED'),(5,'E3','ED'),(6,'F3','ED');
select ID, Prod, Setting from #Table;
Sample data:
IDProdSetting
1EZA1
2EZB2
3EZC2
4EZD2
1EXA1
2EXB2
3EXC2
4EXD2
5EXE3
1EDA1
2EDB2
3EDC2
4EDD2
5EDE3
6EDF3
What seems to be expected output:
ProdSettings
EDA1-B2-C2-D2-E3-F3
EXA1-B2-C2-D2-E3
EZA1-B2-C2-D2
Code used to generate the expected output:
with BaseProducts as (
select distinct Prod from #Table
)
select
bp.Prod,
Settings = stuff((select '-' + Setting
from #Table t1
where t1.Prod = bp.Prod
order by t1.Setting
for xml path(''),TYPE).value('.','varchar(max)'),1,1,'')
from
BaseProducts bp
order by
bp.Prod;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply