October 25, 2010 at 11:55 am
I'm trying to create a query to send to a feed.
As an example, here are my two tables
Table1 has a list of motorcycles
------------------------------
BikeID Name Price
1 Bike1 $xx.xx
2 Bike2 $xx.xx
3 Bike3 $xx.xx
------------------------------
Table2 has a list of pictures for this bike
-------------------------
BikeID Photo
1 B1Pix1.jpg
1 B1Pix2.jpg
1 B1Pix3.jpg
2 B2Pix1.jpg
2 B2Pix2.jpg
3 B3Pix1.jpg
3 B3Pix2.jpg
3 B3Pix3.jpg
3 B3Pix4.jpg
-------------------------
How can I write a query to produce one line for each bike with columns for the bike pictures?
Here is what I want:
---------------------------------------------------------------------
BikeID Name Price image1 image2 image3 image4
1 Bike1 $xx.xx B1Pix1 B1Pix2 B1Pix3
2 Bike2 $xx.xx B2Pix1 B2Pix2
3 Bike3 $xx.xx B3Pix1 B3Pix2 B3Pix3 B3Pix4
----------------------------------------------------------------------
I've looked at CROSS JOINS, PIVOT but can't seem to find a good solution how to generate this. Can anyone steer me in the right directions?
October 25, 2010 at 12:01 pm
Try looking at select ....for xml path ("")
Tom
October 25, 2010 at 12:09 pm
Toms solution will work if you need to get the image names into one column.
But if you need to do a pivot (or cross tab) with separate columns per picture you should have a look at the Cross Tab article referenced in my signature to get a static number of pictures. In order to assign a ordering number to each picture per BikeID you could use ROW_NUMBER() OVER (PARTITION BY BikeID ORDER BY Name).
Once you understand how the CrossTab method works, you should read the DynamicCrossTab article (als referenced in my sig) to pivot a "unknown" (or better flexible) number of pictures.
If you get stuck or have some questions related to the articles I pointed you at please post back.
October 25, 2010 at 3:33 pm
Thanks for the help. I read your article on cross tabs but the examples look like they are from 1 table. I have two tables I am trying to put together in the query. Also I don't really have a column in the second table to use as the pivot column. There is just a list of pictures based on a key field that matches to the other table primary key id.
October 25, 2010 at 4:13 pm
steve-760524 (10/25/2010)
Thanks for the help. I read your article on cross tabs but the examples look like they are from 1 table. I have two tables I am trying to put together in the query. Also I don't really have a column in the second table to use as the pivot column. There is just a list of pictures based on a key field that matches to the other table primary key id.
Well, since the article is really good it's obvious that I can't be the author :-);-) (the article is written by Jeff Moden, one of the top edge folks hanging around here)
The fact that it's based on one table can be resolved to use either a subquery or a CTE (which I would prefer for readability...). To create a column used for the cross tab you could use the ROW_NUMBER() function I mentioned earlier.
Something like
;WITH cte as
(
SELECT
,
ROW_NUMBER() OVER (PARTITION BY BikeID ORDER BY Name) as row
FROM t1 INNER JOIN t2 ON t1.Id=t2.refId
)
SELECT
Name,
MAX(CASE WHEN row=1 THEN col ELSE NULL END) AS image1
FROM cte
GROUP BY Name
October 26, 2010 at 9:00 am
Worked great! Thank you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply