June 7, 2011 at 1:16 pm
This is my first post here. I hope I post everything correctly.
I have a query I am trying to run, but I can't seem to get my images in a horizontal (or tabular) format. I was hoping someone here could shed a little light on it for me. The ultimate goal of this is to output everything to a datareader and generate XML for a web service (which I am doing on the front end). The entire dataset will be about 15,000 records. I just can't get the SQL right. Normally, we would have a db dev do this, but at the moment, we do not have one.
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)
select * from @Autos a
inner join @items i on i.itemid = a.itemid
inner join @Images img on img.itemid = a.itemid
/* 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
*/
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/[/url]
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply