• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

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!
0
erin027
Asked:
erin027
  • 3
  • 2
1 Solution
 
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
 
BinuthCommented:
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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now