selecting multiple columns into one

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 2005 Forums

Transact-SQL (2005)

selecting multiple columns into one

Author

Topic

dprjessie
Starting Member

2 Posts

Posted - 2008-03-18 : 18:16:12

I have a product table that has a productID column and a productName column. Then I have a productCategory table that associates productIDs with categoryIDs. And lastly I have a category table containing a categoryID and categoryName. I want to write a query that will return a table with three columns...productID, productName, and and calculated column containing the categories the product belongs to separated by a comma. A sample output would be...

ProductID____ProductName____Categories
1____________Green Flats____Footwear, Accessories, Women's Apparel
2____________Purple Belt____Accessories, Women's Apparel
3____________Pink Tunic_____Women's Apparel

If any of this isn't clear please feel free to write with questions.
So far I have this query -
SELECT Products.ProductID, ProductName, CategoryName FROM Products, ProductCategory, Categories WHERE Product.ProductID = ProductCategory.ProductID AND Categories.CategoryID = ProductCategory.CategoryID

This returns....
ProductID_____ProductName____CategoryName
1_____________Green Flats____Shoes
1_____________Green Flats____Accessories
1_____________Green Flats____Women's Apparel
2_____________Purple Belt____Accessories
2_____________Purple Belt____Women's Apparel
etc....

Any suggestions on how to remedy this???

dprjessie
Starting Member

2 Posts

Posted - 2008-03-18 : 19:01:54

BTW...
I'd also be happy with...
ProductID___ProductName___CategoryName
1___________Green Flats___Shoes
__________________________Accessories
__________________________Women's Apparel
2___________Purple Belt___Accessories
__________________________Women's Apparel

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-18 : 20:55:05

This would be better done at the front end.

QAZAFI
Yak Posting Veteran

50 Posts

Posted - 2008-03-18 : 21:25:44

Hi Dear
This is quite simple. What you need to do just create a Function like
Create function [dbo].[Get_ProductCategoryText] (@ProductID int)
RETURNS varchar(8000)
AS
BEGIN
declare @w_resulttext varchar(8000)
Set @w_resulttext=''
-- Getting all the text from dbo.Categories against the parameter passed
SELECT
@w_resulttext= @w_resulttext + dbo.Categories.CategoryName +','
FROM dbo.ProductCategory INNER JOIN
dbo.Categories ON dbo.ProductCategory.CategoryID = dbo.Categories.CategoryID
WHERE
dbo.ProductCategory.ProductID = @ProductID
return @w_resulttext
end
and just use this function in your query now your query looks like
SELECT
dbo.Product.ProductID, dbo.Product.ProductNAme,
DBO.Get_ProductCategoryText(dbo.Product.ProductID)
FROM
dbo.Product
and thats it. It will give you the required result
Any question more then welcome

ekb18c
Starting Member

18 Posts

Posted - 2008-03-18 : 21:28:27

I've use the UDF approach and it seems to work ok.

Here is an article that I read about concatenation of columns.

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-18 : 22:55:37

Something like this might get you there


select
  case when row_number() over(partition by ProductID order by ProductID )= 1 then ProductID else null end,
  case when row_number() over(partition by ProductName order by ProductName ) =1 then ProductName else null end, 
CategoryName from
(
select 1 as ProductID, 'Green Flats' as ProductName, 'Shoes' as CategoryName union all
select 1 , 'Green Flats' , 'Access' union all
select 1 , 'Green Flats' , 'wa' union all
select 2 , 'Purple Belt' , 'Access' union all
select 2 , 'Purple Belt' , 'wa' 
) s
group by ProductID, ProductName, CategoryName

You might have to play around with the partition by to break it up a bit better

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources