Combining records with same datatypes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combining records with same datatypes

Author

Topic

liveloveshare
Starting Member

1 Post

Posted - 2011-11-14 : 12:10:40

Hey all.. First time poster. Did a little searching under 'combining records' but didn't find what I was after.

I work for a company that sells crap on the internet. Pretty neat and unique right?

We have multiple tier product tables to represent various products, styles, and options. So the hierarchy goes:

PRODUCTS with the primary key being PR_ID >>>

NEWSTYLES with the primary key being ST_ID linking back to the PRODUCTS table with ST_PRODUCTID >>>

NEWSTYLES_OPTIONS with the primary key being NEWSTYLES_OPTION_ID linkin back to the NEWSTYLES table with Style_ID >>>

here is my query:

select pr_manufactid,name,sum(oneforone) as OneForOne,sum(oneforone_combo) as OneForOne_Combo,sum(multi) as Multi,sum(multi_combo) as Multi_Combo from
(
select b.*
,case when num_styles = 1 and num_options = 1 then 1 else 0 end as OneForOne
,case when num_styles = 1 and num_options > 1 then 1 else 0 end as OneForOne_Combo
,case when num_styles > 1 and num_options = num_styles then 1 else 0 end as Multi
,case when num_styles > 1 and num_options > num_styles then 1 else 0 end as Multi_Combo from
(
select pr_manufactid,name,pr_partnum,pr_title,pr_id as ProductID,count(pr_id) as Num_Styles,sum(Options) as Num_Options from
(
select pr_manufactid,name,pr_partnum,pr_title,pr_id,style_id,count(style_id) as Options
from newstyles_option no
inner join newstyles ns on ns.st_id = no.style_id
inner join products p on p.pr_id = ns.st_productid
inner join manufacturer m on m.categoryid = p.pr_manufactid
where/* pr_manufactid = 73 and*/ st_status=0 and pr_status=0 and isnull(op_status,0)=0
and st_productid in
( select pr_id from products where pr_status = 0 and pr_subcategoryid in
( select subcategoryid from subcategory where categoryid in
( select categoryid from category where type in
( select categoryid from categorytype where categoryid = 15 ))))
group by pr_id,style_id,pr_manufactid,name,pr_partnum,pr_title
)
a
group by pr_id,Options,pr_manufactid,name,pr_partnum,pr_title
)
b
)
c
group by pr_manufactid,name,oneforone,oneforone_combo,multi,multi_combo
order by name

So my results might look something like this:

pr_manufactid name OneForOne OneForOne_Combo Multi Multi_Combo
4 Access Lighting 0 0 12 0
4 Access Lighting 21 0 0 0

OneForOne, OneForOne_Combo, Multi, and Multi_Combo represent the four different scenarios that can come into play.

So I'm trying to effectively consolidate these records. I have designed the case statements such that there will never be more than one scenario occurring for a manufacturer. How can I merge these records efficiently..?

Also if you have any suggestions on how to build my query better in general I'm definitely open to suggestions on that as well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 13:22:55

sounds like below


select pr_manufactid,name,sum(oneforone) as OneForOne,sum(oneforone_combo) as OneForOne_Combo,sum(multi) as Multi,sum(multi_combo) as Multi_Combo from
(
select b.*
,case when num_styles = 1 and num_options = 1 then 1 else 0 end as OneForOne
,case when num_styles = 1 and num_options > 1 then 1 else 0 end as OneForOne_Combo
,case when num_styles > 1 and num_options = num_styles then 1 else 0 end as Multi
,case when num_styles > 1 and num_options > num_styles then 1 else 0 end as Multi_Combo from
(
select pr_manufactid,name,pr_partnum,pr_title,pr_id as ProductID,count(pr_id) as Num_Styles,sum(Options) as Num_Options from
(
select pr_manufactid,name,pr_partnum,pr_title,pr_id,style_id,count(style_id) as Options
from newstyles_option no	
inner join newstyles ns on ns.st_id = no.style_id
inner join products p on p.pr_id = ns.st_productid
inner join manufacturer m on m.categoryid = p.pr_manufactid
where/* pr_manufactid = 73 and*/ st_status=0 and pr_status=0 and isnull(op_status,0)=0 
and st_productid in 
( select pr_id from products where pr_status = 0 and pr_subcategoryid in
( select subcategoryid from subcategory where categoryid in 
( select categoryid from category where type in
( select categoryid from categorytype where categoryid = 15 ))))
group by pr_id,style_id,pr_manufactid,name,pr_partnum,pr_title
)
a
group by pr_id,Options,pr_manufactid,name,pr_partnum,pr_title
)
b
)
c
group by pr_manufactid,name
order by name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources