April 3, 2008 at 12:17 am
Hi guys,
Refer to the table structure as stated below:
create table #prod (pid int, name varchar(10))
insert into #prod values (1,'prod1')
insert into #prod values (2,'prod2')
create table #prod_desc (pid int, pdesc varchar(500))
insert into #prod_desc values (1,'desc1')
insert into #prod_desc values (1,'desc2')
insert into #prod_desc values (1,'desc3')
insert into #prod_desc values (2,'desc5')
insert into #prod_desc values (2,'desc6')
Desired output
Pid Name Desc
1 prod1 desc1,desc2,desc3
2 prod2 desc5,desc6
The above is a sample illustration. There could be N number of products and for each product there could be N number of descriptions.
I need a T-SQL solution either on SQL 2000 or SQL 2005
Appreciate your help.
April 3, 2008 at 6:27 am
One method might be something like:
declare @prod table (pid int, name varchar(10))
insert into @prod values (1,'prod1')
insert into @prod values (2,'prod2')
declare @prod_desc table (pid int, pdesc varchar(500))
insert into @prod_desc values (1,'desc1')
insert into @prod_desc values (1,'desc2')
insert into @prod_desc values (1,'desc3')
insert into @prod_desc values (2,'desc5')
insert into @prod_desc values (2,'desc6')
select
pid,
name,
reverse(substring(reverse(
( select
pdesc + ',' as [data()]
from @prod_desc b
where a.pid = b.pid
for xml path('')
)), 2, 200)) as pdesc
from @prod a
/* -------- Sample Output: --------
pid name pdesc
----------- ---------- ---------------------
1 prod1 desc1, desc2, desc3
2 prod2 desc5, desc6
*/
A bit ragged but it ought to at least get you started.
April 3, 2008 at 8:19 am
I modified your test data as follows:
create table prod (pid int primary key, name varchar(10), pdesc varchar(max))
insert into prod (pid, name) values (1,'prod1')
insert into prod (pid, name) values (2,'prod2')
create table prod_desc (pid int not null, pdesc varchar(500) not null,
constraint PK_Prod_Desc primary key (pid, pdesc))
insert into prod_desc values (1,'desc1')
insert into prod_desc values (1,'desc2')
insert into prod_desc values (1,'desc3')
insert into prod_desc values (2,'desc5')
insert into prod_desc values (2,'desc6')
(Made them permanent tables and added "pdesc" to prod table.)
Then I ran:
create function PDesc
(@PID_in int)
returns varchar(max)
as
begin
declare @Desc varchar(max)
select @desc = coalesce(@desc + ',' + pdesc, pdesc)
from prod_desc
where pid = @pid_in
return @desc
end;
go
update prod
set pdesc = dbo.pdesc(pid)
go
select *
from prod
It seems to get the result you want.
Theoretically, a version of the solution on http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
should work, but I wasn't able to get the right results out of that, nor figure out why it wasn't working. (I've made it work with numbers before, but it didn't want to work with a string function this time. Not sure why.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2008 at 9:21 am
Here is something that worked on the test data.
create table #prod (pid int, name varchar(10), pdesc varchar(max) Constraint PK_Prod Primary Key(pid))
insert into #prod values (1,'prod1', null)
insert into #prod values (2,'prod2', null)
create table #prod_desc (pid int, pdesc varchar(500))
insert into #prod_desc values (1,'desc1')
insert into #prod_desc values (1,'desc2')
insert into #prod_desc values (1,'desc3')
insert into #prod_desc values (2,'desc5')
insert into #prod_desc values (2,'desc6')
Create table #products
(
table_id int identity(1,1),
id int,
prod_name varchar(10),
prod_Desc varchar(50),
prod_descriptions varchar(max)
)
Create Clustered Index UI_Products on #products(id, prod_desc)
INsert Into #products
(
id,
prod_name,
prod_desc
)
Select
P.pid,
P.name,
PD.pdesc
FRom
#prod P Join
#prod_desc PD ON
P.pid = PD.pid
Declare @pdesc varchar(max),
@id int
Update #products
Set @pdesc = prod_descriptions = Case When id = @id Then @pdesc + ', ' + prod_desc Else prod_desc End,
@id = id
From
#products With(Index(UI_Products), tablockx)
Select * from #products where table_id in (Select max(table_id) from #products group by id)
Drop table #products
Drop table #prod
Drop table #prod_desc
I am sure someone will be able to improve this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 3, 2008 at 9:35 am
unfortunately, this alternative requires a temp table since it doesn't seem CTEs can be nested. it works with the #prod_desc table in the original post.
select pid, pdesc,
row_number() over (partition by pid order by pdesc desc) as seq
into #x
from #prod_desc
;
with appender( pid, pdesc, seq )
as (
select pid, cast(pdesc as varchar(max)), seq
from #x where seq = 1
union all
select A.pid, A.pdesc + ' '+ B.pdesc, A.seq
from #x as A
join appender as B
on A.pid = B.pid and A.seq = B.seq + 1
)
select pid, min(pdesc) as full_desc
from appender
group by pid
note that since the row_number() is based on descending description, the descriptions will be alphabetized and the minimum description will be the full set of descriptions.
pid full_desc
1 desc1 desc2 desc3
2 desc5 desc6
once you get that summarization of the multiple descriptions, just join it back to any other table(s).
August 8, 2010 at 5:44 am
antonio.collins (4/3/2008)
unfortunately, this alternative requires a temp table since it doesn't seem CTEs can be nested.
I know it's an old post but what do you mean that CTE's can't be nested?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply