Category
|
Product
|
Pedals
|
LL Mountain Pedal
|
Pedals
|
Touring Pedal
|
Road Bikes
|
Road-150 Red
|
Road Bikes
|
Road-150 Black
|
Pumps
|
Mini-pump
|
And you would like to display it in the following manner:
Category
|
Product
|
Pedals
|
LL Mountain Pedal, Touring Pedal
|
Road Bikes
|
Road-150 Red, Road-150 Black
|
Pumps
|
Mini-pump
|
I have found the following 2 methods to be useful:
Create a function
CREATE FUNCTION [dbo].[GetProductsBySubcategoryID] ( @ProductSubcategoryID int ) RETURNS varchar(max) AS BEGIN declare @output varchar(max) select @output = COALESCE(@output + ', ', '') + Name from Production.Product where ProductSubcategoryID = @ProductSubcategoryID return @output END
Calling the function
SELECT Name, dbo.[GetProductsBySubcategoryID](ProductSubcategoryID) FROM Production.ProductSubcategory GROUP BY Name,ProductSubcategoryID
Pros: Neat, Easy to read/run.
Cons: Slower, You need to create a function for each scenario
Using XMLPath
SELECT SC.Name, ( SELECT Name +',' from Production.Product as P WHERE P.ProductSubcategoryID = SC.ProductSubcategoryID ORDER BY P.Name FOR XML PATH('') ) from Production.ProductSubcategory as SC
Pros: Faster, No need to create a function
Cons: Not easily understandable
Regarding performace, on average the XML method returned 37 rows in 8ms and the former in 6ms.
Some wild symbols also operate as multipliers and can enhance the payout of any winning mixture in which they substitute. Click on 1xbet the slots part, and you will note a dizzying number of games. There are old favorites, whereas new titles are added a daily basis}.
ReplyDelete