RE: Help me build a query!

From: rmartinez (rmartinez_at_discussions.microsoft.com)
Date: 09/10/04 

Date: Thu, 9 Sep 2004 21:13:01 -0700

In case someone hasn't beat me to it this should do it.
But if you are trying to have the column headers be "dynamic" based off new
records being added to the tblProdListing Table, then you have different
issues.

use pubs

create table tblVendorProfile (VendorID int not null, VendorName varchar(50))

Insert into tblVendorProfile (VendorID, vendorName)
Values (1,'Ace')
Insert into tblVendorProfile (VendorID, vendorName)
Values (2,'Beta')

create table tblProdListing (ProdID int not null, ProdName varchar(50))

Insert into tblProdListing (ProdID, ProdName)
Values (1,'Prod1')
Insert into tblProdListing (ProdID, ProdName)
Values (2,'Prod2')
Insert into tblProdListing (ProdID, ProdName)
Values (3,'Prod3')
Insert into tblProdListing (ProdID, ProdName)
Values (4,'Prod4')

create table tblRating (VendorID int not null,ProdID int not null, Rating
int)

Insert into tblRating (VendorID, ProdID, Rating)
Values (1,1,2)
Insert into tblRating (VendorID, ProdID, Rating)
Values (1,2,2)
Insert into tblRating (VendorID, ProdID, Rating)
Values (1,4,3)
Insert into tblRating (VendorID, ProdID, Rating)
Values (2,1,2)
Insert into tblRating (VendorID, ProdID, Rating)
Values (2,4,1)

select vendorName, isnull(MIN(case Prodid
                                when 1
                                    then Rating
                              end),0) as [Prod1],
                   isnull(MIN(case ProdID
                                when 2
                                    then Rating
                              end),0) as [Prod2],
                   isnull(MIN(case ProdID
                                when 3
                                    then Rating
                              end),0) as [Prod3],
                   isnull(MIN(case ProdID
                                when 4
                                    then Rating
                              end),0) as [Prod4]
from tblRating
join tblVendorProfile on tblRating.VendorID = tblVendorProfile.VendorID
Group by VendorName

drop table tblVendorProfile
drop table tblProdListing
drop table tblRating

Results
vendorName Prod1 Prod2
Prod3 Prod4
-------------------------------------------------- ----------- -----------
----------- -----------
Ace 2 2 0
          3
Beta 2 0 0
          1

"Ace" wrote:

> Hi, all.
>
> I am trying to build a query that returns the following record set.
>
> vendor|prod1|prod2|prod3|prod4
> ____________________________
> Ace|1|2|null|3
> Beta|2|null|null|1
>
> tblVendorProfile
> VendorID|VendorName
> _______________________
> 1|Ace
> 2|Beta
>
> tblProdListing
> ProdID|ProdName
> 1|Prod1
> 2|Prod2
> 3|Prod3
> 4|Prod4
>
> tblRating (some prod may not have rating and no record is inserted for those)
> VendorID|ProdID|Rating
> 1|1|2 (Ace on Prod1 has rating 2)
> 1|2|2
> 1|4|3
> 2|1|2
> 2|4|1
>
> Any suggestion will be appreciated!!!
>
>