Select query with defined 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 2000 Forums

SQL Server Development (2000)

Select query with defined columns

Author

Topic

ricc
Starting Member

16 Posts

Posted - 2007-05-06 : 05:21:31

Hi, I have the following two tables

Properties

PropertyID__Name
1____________nu1
2____________nu2
3____________nu3
4____________nu4
5____________nu5

Images

ImageID__PropertyID__ImageUrl
1___________1_________11.jpg
2___________1_________21.jpg
3___________1_________31.jpg
5___________2_________52.jpg
6___________3_________63.jpg
7___________3_________73.jpg
8___________3_________83.jpg

I have to create from the above two table a query that has the following structure, the results have to have 4 Image Url columns even if the property only has a max of 3 images

PropertyID__Name__Image1Url__Image2Url__Image3Url__Image4Url
1____________nu1____11.jpg_____21.jpg_____31.jpg_____null

can anyone provide any pointers?

Many thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-06 : 05:44:48

[code]
select p.PropertyID, p.Name,
Image1Url = max(case when row = 1 then i.ImageUrl end),
Image2Url = max(case when row = 2 then i.ImageUrl end),
Image3Url = max(case when row = 3 then i.ImageUrl end),
Image4Url = max(case when row = 4 then i.ImageUrl end)
from Properties p inner join
(
select PropertyID, ImageUrl,
row = (select count(*) from Images x where x.PropertyID = m.PropertyID and x.ImageID <= m.ImageID)
from Images m
) i
on p.PropertyID = i.PropertyID
group by p.PropertyID, p.Name
[/code]

ricc
Starting Member

16 Posts

Posted - 2007-05-06 : 06:27:09

Thank you very much for your quick reply. Worked great!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources