----------------------------------------------------------- New Message on BDOTNET
----------------------------------------------------------- From: arsalanayub Message 1 in Discussion Concatenating Row values in T-SQL: http://dotnetolympians.wordpress.com/2007/06/01/concatenating-row-values-in-t-sql/ This is one of the oldest topics that one could write on but I am trying to just relight the flame. Open to comments from all. The table: I am using the Products table present in Northwind Database to continue with this example. If you dont have the above database you can download it from here: Products Table structure: Column Name Column Type ProductID int (IDENTITY) ProductName nvarchar(40) SupplierID int CategoryID int QuantityPerUnit nvarchar(20) UnitPrice money UnitsInStock smallint UnitsOnOrder smallint ReorderLevel smallint Discontinued bit Initial data in table: Select CategoryId, ProductName From Products CategoryId ProductName 1 Chai 1 Chang 2 Aniseed Syrup 2 Chef Antons Cajun Seasoning 2 Chef Antons Gumbo Mix 1 Lakkalikööri 2 Original Frankfurter grüne Soße (77 Rows returned) The Requirement to Display data: CategoryId ProductsList 1 Chai,Chang,Guaraná Fantástica,Sasquatch 2 Aniseed Syrup,Chef Antons Cajun Se 3 Pavlova,Teatime Chocolate Biscuits, 4 Queso Cabrales,Queso Manchego La Pa 5 Gustafs Knäckebröd,Tunnbröd,Singap 6 Mishi Kobe Niku,Alice Mutton, 7 Uncle Bobs Organic Dried Pears,Tof 8 Ikura,Konbu,Carnarvon Tigers,Nord-O The solution I like the most: I like using this user defined function named fn_GetProductNameList. CREATE FUNCTION dbo.fn_GetProductNameList ( @CatId int ) RETURNS VarChar(8000) AS BEGIN Declare @buffer VarChar(8000) Select @buffer = IsNull(@buffer + ,, ) + ProductName From Products Where CategoryId = @CatId RETURN @buffer END Query: Select Distinct CategoryId , dbo.fn_GetProductNameList (CategoryId) >From Products To better understand this approach and how this select statement work you need to go through this knowledge base article. So to produce the values in a column as a comma separated values from the same table you can have the following multiple ways as well. 1. Another User Defined function: (fn_GetProductNameList2) CREATE FUNCTION dbo.fn_GetProductNameList2 ( @CatId Int ) RETURNS VarChar(8000) AS BEGIN Declare @t Table ( p VarChar(40) ) Declare @r VarChar(8000) Set @r = SPACE(0) Insert @t ( p ) select ProductName From Products Where CategoryId = @CatId IF @@ROWCOUNT > 0 Update @t SET @r = @r + p + , RETURN(@r) END Query: Select CategoryId, dbo.fn_GetProductNameList2(CategoryId) >From Products Group By CategoryId These functions above fn_GetProductNameList and fn_GetProductNameList2 are widely used but are not recommended approach because of the fact that they rely on the physical implementation. Any changes in the indexing, statistics or else can make them unreliable. 2. The Cursor: CREATE FUNCTION dbo.fn_GetProductNameList3 ( @CatId Int ) RETURNS VarChar(4000) AS BEGIN Declare @ProductName Varchar(4000) Set @ProductName = Declare @CurVar Varchar(255) Declare Cur_ProductName Cursor For Select Distinct ProductName From Products Where CategoryId = @CatId Open Cur_ProductName Fetch Cur_ProductName Into @CurVar While (@@Fetch_Status <> -1) Begin Set @ProductName = @ProductName + @CurVar + , Fetch Next From Cur_ProductName Into @CurVar End Close Cur_ProductName Deallocate Cur_ProductName If(LTrim(RTrim(@ProductName)) <> ) Return Substring(LTrim(RTrim(@ProductName)), 0, Len(RTrim(@ProductName)) -1) Return LTrim(RTrim(@ProductName)) End Query: Select CategoryId, dbo.fn_GetProductNameList3(CategoryId) >From Products Group By CategoryId This user defined function contains a cursor to do the magic. Its more reliable but the drawbacks of cursor on performance are know to all so they should be avoided and loops used instead. 3. Loop inside a user defined function: (Linda Wierzbecki) CREATE FUNCTION dbo.fn_GetProductNameList4() RETURNS @tempProducts TABLE ( CategoryId Int, Product VarChar (40), ListProductNames VarChar (8000) ) BEGIN Insert @tempProducts (CategoryId, Product, ListProductNames) Select CategoryId, MIN(ProductName), MIN(ProductName) From Products Group By CategoryId While ( Select COUNT(Product) From @tempProducts ) > 0 BEGIN Update TP Set ListProductNames = ListProductNames + COALESCE( ( Select , + MIN( ProductName ) From Products Where Products.CategoryId = TP.CategoryId AND Products.ProductName > TP.Product), ), Product = ( Select MIN(ProductName) From Products Where Products.CategoryId = TP.CategoryId AND Products.ProductName > TP.Product ) From @tempProducts TP END RETURN END Query: Select CategoryId, ListProductNames AS Products From dbo.fn_GetProductNameList4() This approach is also widely used. 4. Recursive User Defined Function: CREATE FUNCTION dbo.fn_GetProductNameList5 ( @CatId Int, @i Int ) RETURNS VarChar (8000) AS BEGIN Declare @r Varchar (8000) Declare @l Varchar (8000) Select @i = @i - 1, @r = ProductName + , From Products p1 Where CategoryId = @CatId AND @i = ( Select COUNT(*) From Products p2 Where p2.CategoryId = p1.CategoryId AND p2.ProductName <= p1.ProductName ) If @i > 0 BEGIN Exec @l = dbo.fn_GetProductNameList5 @CatId, @i Set @r = @l + @r END RETURN @r ; END Query: Select CategoryId, dbo.fn_GetProductNameList5 ( CategoryId, COUNT(ProductName) ) >From Products Group By CategoryId ; This approach uses recursion so before applying it keep in mind that the maximum nest level in T-SQL is 32. 5. Pivoting Method: Select CategoryId, MAX( CASE seq When 1 Then ProductName Else END ) + , + MAX( CASE seq When 2 Then ProductName Else END ) + , + MAX( CASE seq When 3 Then ProductName Else END ) + , + MAX( CASE seq When 4 Then ProductName Else END ) From ( Select p1.CategoryId, p1.ProductName, ( Select COUNT(*) From Products p2 Where p2.CategoryId = p1.CategoryId AND p2.ProductName <= p1.ProductName ) From Products p1 ) D ( CategoryId, ProductName, seq ) Group By CategoryId ; Well I tried this method in my production environment and it fails or shows some abnormal behavior as the size of the table increases. Its best to use with smaller datasets only. So here you are with the solutions. Which one would you use and why? http://dotnetolympians.wordpress.com/2007/06/01/concatenating-row-values-in-t-sql/ UG Leader DotNetOlympians Blog ----------------------------------------------------------- To stop getting this e-mail, or change how often it arrives, go to your E-mail Settings. http://groups.msn.com/BDotNet/_emailsettings.msnw Need help? If you've forgotten your password, please go to Passport Member Services. http://groups.msn.com/_passportredir.msnw?ppmprop=help For other questions or feedback, go to our Contact Us page. http://groups.msn.com/contact If you do not want to receive future e-mail from this MSN group, or if you received this message by mistake, please click the "Remove" link below. On the pre-addressed e-mail message that opens, simply click "Send". Your e-mail address will be deleted from this group's mailing list. mailto:[EMAIL PROTECTED]
