Associative Entity - Combining result

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)

Associative Entity - Combining result

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2008-03-12 : 07:34:40

Hi,

I have the following table

(1) Item Table
 ItemID      Name
 1          Sample Item

(2) Brand Table
 BrandID    Name
 1         Sample Brand - 1
 2         Sample Brand - 2


(3) ItemBrand Table
 ItemID BrandID
 1             1
 1             2


Desired Output

ItemID     ItemName        Brand1             Brand2              
--------------------------------------------------------
1         Sample Item  Sample Brand - 1    Sample Brand - 2

How to get the desired output?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 07:37:04

Something similar to

SELECT i.ItemID, i.Name,
MAX(CASE WHEN ib.BrandID = 1 THEN b.Name ELSE '' END) AS Brand1,
MAX(CASE WHEN ib.BrandID = 2 THEN b.Name ELSE '' END) AS Brand2
FROM Item AS i
INNER JOIN ItemBrand AS ib ON ib.ItemID = i.ItemID
INNER JOIN Brand AS b ON b.BrandID = ib.BrandID
GROUP BY i.ItemID, i.Name

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2008-03-12 : 07:55:23

thanks a ton

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources