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!
erin027Asked:
Who is Participating?
 
BinuthConnect With a Mentor Commented:
not sure , this is looking for
SELECT 
	BoardID,
        ProductID,
        ProductName,
        (SELECT distinct cast(PhotoID as varchar) + ','
         FROM Photo  
         WHERE ProductID = A.ProductID
         FOR XML PATH('')
        ) AS PhotoIDs
FROM 
(       Select distinct p.ProductID, B.BoardID,ProductName
        From Product p
		inner join Together T on T.ProductID = p.ProductID
		inner join Board B on T.BoardID = B.BoardID
        Left Join Photo ph ON ph.ProductID = p.ProductID
) AS A

Open in new window

0
 
BinuthCommented:
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

0
 
erin027Author Commented:
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
0
 
BinuthCommented:
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

0
 
erin027Author Commented:
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!


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.