asked on

MS SQL, Grouping

I am using MS SQL 2005
Let say I have 2 tables:

Product Photos
--------- -------------
ProductID PhotoID
ProductName ProductID

And query something like this:
Select p.ProductID,ProductName,PhotoID
From Product p
Left Join Photo ph ON ph.ProductID=p.ProductID

The result came out to be:

ProductID ProductName PhotoID
1 Car 1
1 Car 2
1 Car 3
2 Boat 4
3 Toy Null

I WANT THE RESULT TO BE LIKE BELOW:

ProductID ProductName PhotoID
1 Car 1,2,3
2 Boat 4
3 Toy Null

How do I do this?
Thank you!

Binuth

try this sample sql

declare @test as table(ProductID INT,ProductName VARCHAR(50),PhotoID INT)
insert into @test values(1,'Car',1)
insert into @test values(1,'Car',2)
insert into @test values(1,'Car',3)
insert into @test values(2,'Boat',3)
insert into @test values(3,'Toy',NULL)
 
 
SELECT 
	ProductID,
	ProductName,
	(SELECT distinct cast(PhotoID as varchar) + ','
	 FROM @test  
	 WHERE ProductID = A.ProductID
	 FOR XML PATH('')
	) AS PhotoIDs
FROM 
(SELECT DISTINCT ProductID,ProductName FROM @test) AS A

Open in new window

erin027

ASKER

Binuth:
I have a quick question though...
I have over a 1000 data in both of the table and about a half of the product doesn't have photo.
It's impossible for me to insert @test values manually.
Is there another way to solve this?
Thanks

Binuth

Hmm... there is no need for Table varable(my last comment was just a sample, i think you made change)


SELECT 
	ProductID,
	ProductName,
	(SELECT distinct cast(PhotoID as varchar) + ','
	 FROM Photo  
	 WHERE ProductID = A.ProductID
	 FOR XML PATH('')
	) AS PhotoIDs
FROM 
(	Select distinct p.ProductID,ProductName
	From Product p
	Left Join Photo ph ON ph.ProductID=p.ProductID
) AS A

Open in new window

erin027

ASKER

I am so sorry...
I am very new with MS SQL and I am sure your query works but my situation is little different now. So let me just explain it fully. Everything is same, but there is one more Table and I don't know how to implement it.

Board Together ProductID
---------------- ----------------- ---------------
BoardID TogetherID ProductID
BoardID ProductName
ProductID

I want to select BoardID and ProductName. And each BoardID has one or more Product and sometimes BoardID doesn't have a product.

I really appreciate your help and sorry for the confusion!

ASKER CERTIFIED SOLUTION

Binuth

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial