Add strings - SQL Server Forums

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

Transact-SQL (2000)

Add strings

Author

Topic

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2005-06-28 : 16:09:36

I have two tables -

Inventory (InventoryID, ProdName)

Photo (PhotoID, InventoryID, PhotoURL)

The data on the table is like this -

Inventory -
InventoryID ProdName
1 Name1
2 Name2

Photo -
PhotoID InventoryID PhotoURL
1 1 http://myimageserver/imageName1_1.jpg
2 1 http://myimageserver/imageName1_2.jpg
3 1 http://myimageserver/imageName1_3.jpg
4 2 http://myimageserver/imageName2_1.jpg
5 2 http://myimageserver/imageName2_2.jpg

I want to use one query to return everything from the Inventory table, and addition of PhotoURLs delimited by comma from Photo table. The result should be like this -

InventoryID ProdName PhotoURL
1 Name1 http://myimageserver/imageName1_1.jpg,http://myimageserver/imageName1_2.jpg,http://myimageserver/imageName1_3.jpg

2 Name2 http://myimageserver/imageName2_1.jpg,http://myimageserver/imageName2_1.jpg

Thanks in advance,

George

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-28 : 16:29:01

[code]
Create Table myTable99 (imageId int, inventoryId int, PhotoUrl varchar(100))
Insert Into myTable99
Select 1, 1, 'http://myimageserver/imageName1_1.jpg' Union All
Select 2, 1, 'http://myimageserver/imageName1_2.jpg' Union All
Select 3, 1, 'http://myimageserver/imageName1_3.jpg' Union All
Select 4, 2, 'http://myimageserver/imageName2_1.jpg' Union All
Select 5, 2, 'http://myimageserver/imageName2_2.jpg'

go
create function dbo.getInventoryPhotos(@inventoryId int)
Returns varchar(8000) As
Begin
Declare @photoList varchar(8000)

Select @photoList = isnull(@photoList+',','') + photoUrl From myTable99 Where inventoryId=@inventoryId

Return(@photoList)
End
go

Select distinct inventoryId, dbo.getInventoryPhotos(inventoryId)
From myTable99
go
Drop Table myTable99
go
Drop function dbo.getInventoryPhotos
[/code]

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2005-06-28 : 16:33:14

Very cool. Thanks a lot.

George

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources