Monday, September 19, 2011

T-SQL: Get multiple values in CSV format

There are some instances when you need to retrieve a number of values in one column seperated by a comman.  Consider this set of data:


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.



No comments:

Post a Comment