Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1213141516»»»

Concatenating Rows Expand / Collapse
Author
Message
Posted Friday, March 4, 2011 1:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 16, 2014 8:07 AM
Points: 2, Visits: 49
Sorry Guys, as I remember the CTE recursion works with depth <= 70


Post #1073556
Posted Friday, March 4, 2011 3:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 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 #test
DECLARE @fruit VARCHAR(MAX)
SELECT @fruit = COALESCE(@fruit + '', '') + name
FROM #test
SELECT Colors = @fruit
It take no efford at all.

Artur
Post #1073597
Posted Friday, March 4, 2011 5:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 5, 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.Name
FROM
Warehouse_FG a JOIN NPF_FGWarehouse b on b.WarehouseID = a.ID
WHERE
b.NPFID = @DocumentID
ORDER BY
a.Name
RETURN
@Warehouse_List
END
Post #1073629
Posted Friday, March 4, 2011 9:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 #test
DECLARE @fruit VARCHAR(MAX)
SELECT @fruit = COALESCE(@fruit + '', '') + name
FROM #test
SELECT Colors = @fruit
It take no efford at all.

Artur


This 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 fruit
SELECT @joined

Post #1073655
Posted Saturday, March 5, 2011 9:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 36,710, Visits: 31,158
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.Name
FROM
Warehouse_FG a JOIN NPF_FGWarehouse b on b.WarehouseID = a.ID
WHERE
b.NPFID = @DocumentID
ORDER BY
a.Name
RETURN
@Warehouse_List
END


That 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1073718
Posted Saturday, March 5, 2011 9:53 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 36,710, Visits: 31,158
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 #test
DECLARE @fruit VARCHAR(MAX)
SELECT @fruit = COALESCE(@fruit + '', '') + name
FROM #test
SELECT Colors = @fruit
It take no efford at all.

Artur


Because 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1073721
Posted Saturday, March 5, 2011 10:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 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" RBAr

Its 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 opinion


Artur

The answer to all questions about SQL Server is: It depends..., except "Should auto-shrink be enabled"

Post #1073726
Posted Saturday, March 5, 2011 11:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 36,710, Visits: 31,158
arturv (3/5/2011)
Sometimes the "best practices programming" its not best or more profitable solution.
But this is just an opinion



Then 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1073752
Posted Sunday, March 6, 2011 5:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 6, 2011 5:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
« Prev Topic | Next Topic »

Add to briefcase «««1213141516»»»

Permissions Expand / Collapse