June 7, 2011 at 2:17 pm
Nice job posting usable DDL, DML and expected results!
This should get you there:
DECLARE @Autos TABLE
(
itemid int
,make varchar(50)
,model varchar(50)
)
INSERT INTO @Autos VALUES (1000,'BMW','M3')
INSERT INTO @Autos VALUES (1001,'Audi','S3')
INSERT INTO @Autos VALUES (1002,'Nissan','370Z')
INSERT INTO @Autos VALUES (1003,'Infiniti','G37')
DECLARE @Items TABLE
(
itemid int
,shortdesc varchar(50)
,contactname varchar(50)
)
INSERT INTO @Items VALUES (1000,'BMW Description','Car Guy')
INSERT INTO @Items VALUES (1001,'Audi Description','Car Guy')
INSERT INTO @Items VALUES (1002,'Nissan Description','Car Guy')
INSERT INTO @Items VALUES (1003,'Infiniti Description','Car Guy')
DECLARE @Images TABLE
(
imageid int
,itemid int
,imagename varchar(50)
,isPrimary bit
)
INSERT INTO @Images VALUES (1, 1000,'image_bmw_1.jpg',1)
INSERT INTO @Images VALUES (2, 1000,'image_bmw_2.jpg',0)
INSERT INTO @Images VALUES (3, 1000,'image_bmw_3.jpg',0)
INSERT INTO @Images VALUES (4, 1001,'image_audi_1.jpg',1)
INSERT INTO @Images VALUES (5, 1001,'image_audi_2.jpg',0)
INSERT INTO @Images VALUES (6, 1001,'image_audi_3.jpg',0)
INSERT INTO @Images VALUES (7, 1002,'image_nissan_1.jpg',1)
INSERT INTO @Images VALUES (8, 1003,'image_infiniti_1.jpg',1)
INSERT INTO @Images VALUES (9, 1003,'image_infiniti_2.jpg',0);
WITH cte
AS (
SELECT a.itemid,
a.make,
a.model,
i.shortdesc,
i.contactname,
img.imagename,
ROW_NUMBER() OVER (PARTITION BY img.itemid ORDER BY img.isPrimary DESC, img.itemid) AS image_num
FROM @Autos a
INNER JOIN @items i ON i.itemid = a.itemid
INNER JOIN @Images img ON img.itemid = a.itemid
)
SELECT itemid,
make,
model,
shortdesc,
contactname,
MAX(CASE WHEN image_num = 1 THEN imagename ELSE NULL END) AS image01,
MAX(CASE WHEN image_num = 2 THEN imagename ELSE NULL END) AS image02,
MAX(CASE WHEN image_num = 3 THEN imagename ELSE NULL END) AS image03
FROM cte
GROUP BY itemid,
make,
model,
shortdesc,
contactname
/* DESIRED OUTPUT
itemid,make,model,shortdesc,contactname,image01,image02,image03
1000,BMW,M3,BMW Description,Car Guy,image_bmw_1.jpg,image_bmw_2.jpg,image_bmw_3.jpg
...
...
1002,Nissan,370Z,Nissan Description,Car Guy,image_nissan_1.jpg,NULL,NULL
*/
Here is the reference article where all credit goes for explaining this technique: http://www.sqlservercentral.com/articles/T-SQL/63681/%5B/url%5D
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2011 at 2:31 pm
Thanks a million!
I was really close on my own. I knew that I could use a CTE and possibly use the row_number() as an "index". I couldn't work out how to run through them one by one. I was messing around with COALESCE and MAX, but it looks like you nailed it!
June 7, 2011 at 2:45 pm
Daniel H (6/7/2011)
Thanks a million!I was really close on my own. I knew that I could use a CTE and possibly use the row_number() as an "index". I couldn't work out how to run through them one by one. I was messing around with COALESCE and MAX, but it looks like you nailed it!
You're very welcome 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply