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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy