January 9, 2012 at 3:02 pm
I know this has been asked alot and I have read through a lot of articles on using pivots, row_number partition, etc. but I can't seem to find one I need help with. I am trying to write a query that will list my pictures in a separate column. Here is a sample of what I have
Table1
ProductID, SKU, Description
1,12345,Shirt
2,5678,Shirt2
3, 3456, Another Shirt
Table2 has the pictures
ProductID, Picture
1, 1.jpg
1, 2.jpg
1, 3.jpg
2, 4.jpg
2, 5.jpg
3, 6.jpg
3, 7.jpg
3, 8.jpg
This is what I want to return
ProductID, SKU, Description, Pix1, Pix2, Pix3
1, 12345, Shirt, 1.jpg, 2.jpg, 3.jpg
2, 5678, Shirt2, 4.jpg, 5.jpg, null
3, 3456, Another Shirt, 6.jpg, 7.jpg, 8.jpg
January 9, 2012 at 4:54 pm
Join the two tables and pivot the data using the CrossTab method as described in the related link in my signature should do the trick.
January 9, 2012 at 5:42 pm
If you want to retrieve all the pictures to a single column, you can use similar syntax:
declare @table1 as table (
ProductId int
,SKU varchar(10)
,[Description] varchar(50)
)
insert into @table1
(ProductId, SKU, Description)
select 1,'1234','Shirt' union
select 2,'5678','Shirt2' union
select 3,'3456','Another Shirt'
declare @table2 as table(
ProductId int
,Picture varchar(50)
)
insert into @table2
select 1,'1.jpg' union
select 1,'2.jpg' union
select 1,'3.jpg' union
select 2,'4.jpg' union
select 2,'5.jpg' union
select 2,'6.jpg' union
select 3,'7.jpg' union
select 3,'8.jpg' union
select 3,'9.jpg'
select
t1.ProductId
,t1.SKU
,t1.[Description]
,stuff((select ',' + t2.Picture from @table2 as t2 where t1.ProductId = t2.ProductId for xml path('')),1,1,'') as Picture
from
@table1 as t1
--------
Manjuke
http://www.manjuke.com
January 10, 2012 at 7:41 am
LutzM (1/9/2012)
Join the two tables and pivot the data using the CrossTab method as described in the related link in my signature should do the trick.
Thanks for the info but looking at your CrossTab method, it looks like you have a key to create the columns. You look for WHEN QUARTER = 1 you know what to use. I don't have a key to reference. They are just random picture names for each product.
January 10, 2012 at 7:42 am
manjuke (1/9/2012)
If you want to retrieve all the pictures to a single column, you can use similar syntax:
declare @table1 as table (
ProductId int
,SKU varchar(10)
,[Description] varchar(50)
)
insert into @table1
(ProductId, SKU, Description)
select 1,'1234','Shirt' union
select 2,'5678','Shirt2' union
select 3,'3456','Another Shirt'
declare @table2 as table(
ProductId int
,Picture varchar(50)
)
insert into @table2
select 1,'1.jpg' union
select 1,'2.jpg' union
select 1,'3.jpg' union
select 2,'4.jpg' union
select 2,'5.jpg' union
select 2,'6.jpg' union
select 3,'7.jpg' union
select 3,'8.jpg' union
select 3,'9.jpg'
select
t1.ProductId
,t1.SKU
,t1.[Description]
,stuff((select ',' + t2.Picture from @table2 as t2 where t1.ProductId = t2.ProductId for xml path('')),1,1,'') as Picture
from
@table1 as t1
Thanks for the reply. I need each picture in it's own column.
January 10, 2012 at 8:49 am
Well then as 'Lutz' mentioned, you can do it using the PIVOT. Please see the code below. I took the sample code that Lutz had mentioned in his post.
declare @table1 as table (
ProductId int
,SKU varchar(10)
,[Description] varchar(50)
)
insert into @table1
(ProductId, SKU, Description)
select 1,'1234','Shirt' union
select 2,'5678','Shirt2' union
select 3,'3456','Another Shirt'
declare @table2 as table(
ProductId int
,Picture varchar(50)
)
insert into @table2
select 1,'1.jpg' union
select 1,'2.jpg' union
select 1,'3.jpg' union
select 2,'4.jpg' union
select 2,'5.jpg' union
select 3,'6.jpg' union
select 3,'7.jpg' union
select 3,'8.jpg'
;with cte_pivot as(
select
ProductId
,[1] as Pix1
,[2] as Pix2
,[3] as Pix3
from (
select
ProductID,
Picture,
ROW_NUMBER() over(partition by ProductID order by ProductID) as PicID
from
@table2
) as Src
pivot (Max(Picture) for PicID IN ([1],[2],[3])) as Pvt
)
select
t1.ProductId
,t1.SKU
,t1.Description
,t2.Pix1
,t2.Pix2
,t2.Pix3
from
@table1 as t1
join cte_pivot as t2 on t1.ProductId = t2.ProductId
If this is what you want, then give a big thanks to Lutz. It was his sample which did the trick. I just built the solution from it.. 😎
--------
Manjuke
http://www.manjuke.com
January 10, 2012 at 9:02 am
And here's the CrossTab solution I had in mind:
;
WITH cte AS
(
SELECT
ProductId,
Picture,
ROW_NUMBER() OVER(PARTITION BY ProductId ORDER BY Picture ) AS row
FROM @table2
)
SELECT
t1.ProductId,
t1.SKU,
t1.[Description],
MAX(CASE WHEN t2.row=1 THEN t2.Picture ELSE NULL END) AS Pix1,
MAX(CASE WHEN t2.row=2 THEN t2.Picture ELSE NULL END) AS Pix2,
MAX(CASE WHEN t2.row=3 THEN t2.Picture ELSE NULL END) AS Pix3
FROM @table1 t1
INNER JOIN cte t2 ON t1.ProductId=t2.ProductId
GROUP BY
t1.ProductId,
t1.SKU,
t1.[Description]
As you can see, there is a key reference. It's just not as obvious as in most other cases... 😉
@Manjuke: Thank you for posting ready to use sample data! Made it a lot easier to provide the solution (strong hint for steve... 😉 )
January 10, 2012 at 9:41 am
Perfect!! Thanks to both of you for helping me with this. It works perfect for what I need.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply