Sql table Rows to add another table as columns

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 2012 Forums

Transact-SQL (2012)

Sql table Rows to add another table as columns

Author

Topic

cengaver
Starting Member

34 Posts

Posted - 2015-03-23 : 14:04:12

Hello,
I have two table PRODUCT and PRODUCT_IMAGES. PRODUCT_IMAGES table has PRODUCT_ID. I want to get Product images in one row.

Forexample:

ID Name Order ... ImageUrl1 ImageUrl2 ImageUrl3
1 Apple 5 ... abc.jpg asd.jpg aaa.jpg

How can i do like this?

Could you help me please ?

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-23 : 14:27:11

something like:

select product_id, product_name, product_order, ImageUrls
from Product p
cross apply
(
select Image_URL + ' '
from Product_images i
where i.product_id = p.product_id
for xml path('')
) _(ImageUrls)
[/code]

cengaver
Starting Member

34 Posts

Posted - 2015-03-23 : 14:43:07

thanks for your answer gbritton

I need to Image_urls as column. is it possible ?

cengaver
Starting Member

34 Posts

Posted - 2015-03-23 : 14:59:20

is it possible like this;
product_id, product_name, image_url1, image_url2, iamage_url3

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-23 : 15:02:42

Use PIVOT for that.

https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

cengaver
Starting Member

34 Posts

Posted - 2015-03-23 : 16:33:50

i couldnt do this using pivot because it needs aggregate function.

May be i couldnt understand.

PRODUCT TABLE
------------------
ID NAME COLOR SIZE
1 Tshirt Red M
2 Shoes Blue M

PRODUCT_IMAGES TABLE
-------------------------
ID PRODUCT_ID IMAGE_NAME
1 1 abc.jpg
2 1 xyz.jpg
3 1 aaa.jpg

These my tables. When i execute query. i need to output like this;

OUTPUT
-------
ID NAME COLOR SIZE IMAGE_NAME1 IMAGE_NAME2 IMAGE_NAME3
1 Tshirt Red M abc.jpg xyz.jpg aaa.jpg

Please help me ?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-23 : 19:13:36

this may be simpler. How many images per product, maximum?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-24 : 10:33:21

This works for me:


declare @p table(ID int, Name varchar(20), Color varchar(20), Size char(1))
insert into @p(id, Name, Color, Size) values

--PRODUCT TABLE
------------------
--ID NAME COLOR SIZE
(1,'Tshirt','Red ', 'M'),
(2,'Shoes ','Blue', 'M')

declare @i table(id int, product_id int, image_name varchar(20))
insert into @i (id, product_id, image_name) values

--PRODUCT_IMAGES TABLE
---------------------------
--ID PRODUCT_ID IMAGE_NAME
(1, 1, 'abc.jpg'),
(2, 1, 'xyz.jpg'),
(3, 1, 'aaa.jpg')

select id, [1],[2],[3]
from
(
	select p.id, i.image_name, i.id as image_id
	from @p p
	join @i i on i.product_id = p.id
) src

pivot (max(image_name) for image_id in ([1],[2],[3])) pvt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources