Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Concatenating Rows Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, March 04, 2011 1:21 PM
 Forum Newbie Group: General Forum Members Last Login: Thursday, February 13, 2014 11:23 AM Points: 2, Visits: 48
 Sorry Guys, as I remember the CTE recursion works with depth <= 70
Post #1073556
 Posted Friday, March 04, 2011 3:17 PM
 Grasshopper Group: General Forum Members Last Login: Thursday, May 02, 2013 7:30 AM Points: 11, Visits: 81
 Hi guys,why dont we keep it simple?CREATE TABLE #test (id INT NULL, NAME NVARCHAR(20) NULL)INSERT INTO #test ( id, name )VALUES ( 101, 'Apple' ), ( 102, 'Banana' ), ( 103, 'Orange' ), ( 104, 'Melon' ), ( 105, 'Grape' )SELECT * FROM #testDECLARE @fruit VARCHAR(MAX) SELECT @fruit = COALESCE(@fruit + '', '') + nameFROM #test SELECT Colors = @fruitIt take no efford at all.Artur
Post #1073597
 Posted Friday, March 04, 2011 5:26 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, November 05, 2013 5:50 PM Points: 1, Visits: 8
 Just curious as to why you didn't consider the built-in T-SQL function called COALESCE. I often need to do what you describe and have found that COALESCE is relatively simple code and performs well. I use a function similar to the following:CREATE FUNCTION dbo.udf_Get_FG_Warehouse_List (@DocumentID int) RETURNS varchar(1000) AS BEGIN DECLARE @Warehouse_List varchar(1000)SELECT @Warehouse_List = COALESCE(@Warehouse_List + ', ', '') + a.NameFROM Warehouse_FG a JOIN NPF_FGWarehouse b on b.WarehouseID = a.IDWHERE b.NPFID = @DocumentIDORDER BY a.NameRETURN @Warehouse_ListEND
Post #1073629
 Posted Friday, March 04, 2011 9:07 PM
 Grasshopper Group: General Forum Members Last Login: Saturday, March 10, 2012 8:06 PM Points: 15, Visits: 51
 arturv (3/4/2011)Hi guys,why dont we keep it simple?CREATE TABLE #test (id INT NULL, NAME NVARCHAR(20) NULL)INSERT INTO #test ( id, name )VALUES ( 101, 'Apple' ), ( 102, 'Banana' ), ( 103, 'Orange' ), ( 104, 'Melon' ), ( 105, 'Grape' )SELECT * FROM #testDECLARE @fruit VARCHAR(MAX) SELECT @fruit = COALESCE(@fruit + '', '') + nameFROM #test SELECT Colors = @fruitIt take no efford at all.ArturThis is what I was thinking, and it what was several others already mentioned. Here is a "WITH" version of that one from Artur:`DECLARE @joined VARCHAR(max);WITH fruit as ( select 'Apple' as name, 101 as id union all select 'Banana' as name, 102 as id union all select 'Orange' as name, 103 as id union all select 'Melon' as name, 104 as id union all select 'Grape' as name, 105 as id )SELECT @joined=COALESCE(@joined+',', '')+name FROM fruitSELECT @joined`
Post #1073655
 Posted Saturday, March 05, 2011 9:49 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 8:45 AM Points: 35,576, Visits: 29,813
 peter.stanford 73369 (3/4/2011)Just curious as to why you didn't consider the built-in T-SQL function called COALESCE. I often need to do what you describe and have found that COALESCE is relatively simple code and performs well. I use a function similar to the following:CREATE FUNCTION dbo.udf_Get_FG_Warehouse_List (@DocumentID int) RETURNS varchar(1000) AS BEGIN DECLARE @Warehouse_List varchar(1000)SELECT @Warehouse_List = COALESCE(@Warehouse_List + ', ', '') + a.NameFROM Warehouse_FG a JOIN NPF_FGWarehouse b on b.WarehouseID = a.IDWHERE b.NPFID = @DocumentIDORDER BY a.NameRETURN @Warehouse_ListENDThat works fine but is a form of RBAR because the function is necessarily a scalar function which refers to a table. That can make performance actually worse than a cursor. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1073718
 Posted Saturday, March 05, 2011 9:53 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 8:45 AM Points: 35,576, Visits: 29,813
 arturv (3/4/2011)Hi guys,why dont we keep it simple?CREATE TABLE #test (id INT NULL, NAME NVARCHAR(20) NULL)INSERT INTO #test ( id, name )VALUES ( 101, 'Apple' ), ( 102, 'Banana' ), ( 103, 'Orange' ), ( 104, 'Melon' ), ( 105, 'Grape' )SELECT * FROM #testDECLARE @fruit VARCHAR(MAX) SELECT @fruit = COALESCE(@fruit + '', '') + nameFROM #test SELECT Colors = @fruitIt take no efford at all.ArturBecause that's RBAR an solves only for one scalar return. Also, if the list is long, the code begins to slow down almost exponentially because it has to keep rebuilding the variable internally as well as growing the memory allocation for the variable which is also relatively expensive. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1073721
 Posted Saturday, March 05, 2011 10:20 AM
 Grasshopper Group: General Forum Members Last Login: Thursday, May 02, 2013 7:30 AM Points: 11, Visits: 81
 Hi Jeff,Thanks for your reply.You're right... this can be used as an example of RBAR.But if we consider that this specific operation is limited to 8000 characters, its a very "small" RBArIts all comes down to what is pretended, how much it costs to develop, and the time you have, the available resources, etc...Because there are many ways to achieve the same objective, we always show look for alternatives and choose the one that serves us better.Sometimes the "best practices programming" its not best or more profitable solution.But this is just an opinionArturThe answer to all questions about SQL Server is: It depends..., except "Should auto-shrink be enabled"
Post #1073726
 Posted Saturday, March 05, 2011 11:37 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 8:45 AM Points: 35,576, Visits: 29,813
 arturv (3/5/2011)Sometimes the "best practices programming" its not best or more profitable solution.But this is just an opinionThen you won't mind me expressing my opinion. It takes no longer to do things correctly than it does to fall back on RBAR. The key is that you have to be well practiced enough to know the set based method instead of falling back on RBAR. The only way to do that is to practice.Also, NEVER justify the use of RBAR just because of a supposedly small number of rows because you don't actually know what someone else will do with either the tables involved or the code. It'll also make you "weak" because, instead of practicing good set based methods, you keep using RBAR. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1073752
 Posted Sunday, March 06, 2011 5:16 AM
 Grasshopper Group: General Forum Members Last Login: Saturday, April 16, 2011 10:43 PM Points: 19, Visits: 39
 Another simple way of doing this ... see the example at http://www.sqlsuperfast.com/post/2011/02/17/T-SQL-to-Convert-rows-into-single-column.aspx
Post #1073828
 Posted Sunday, March 06, 2011 5:18 AM
 Grasshopper Group: General Forum Members Last Login: Saturday, April 16, 2011 10:43 PM Points: 19, Visits: 39
 another simple way of doing this see at http://www.sqlsuperfast.com/post/2011/02/17/T-SQL-to-Convert-rows-into-single-column.aspx
Post #1073829

 Permissions