Concatenating Rows

Tom Winter (10/14/2009)
Thanks Adam. I just found that solution somewhere else right after I posted. Here's all my references:
Coalesce is not the answer to string concatentation in T-SQL
http://msmvps.com/blogs/robfarley/archive/2007/04/08/coalesce-is-not-the-answer-to-string-concatentation-in-t-sql.aspx
FOR Clause (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms173812(SQL.90).aspx
TYPE Directive in FOR XML Queries
http://msdn.microsoft.com/en-us/library/ms190025(SQL.90).aspx
value() Method (xml Data Type)
http://msdn.microsoft.com/en-us/library/ms178030(SQL.90).aspx
XML Path Language (XPath) - 2.5 Abbreviated Syntax
http://www.w3.org/TR/xpath#path-abbrev

Tom, thanks for taking the time to post these excellent references for using the FOR XML PATH solution!

Ken SSCoach Points: 17191 More actions · Answer 5

Joel, you are correct, Visual Studio is all that you need to set up CLR functions. I would agree however that understanding what the assembly is doing and being able to modify that code can be challenging if you do not use C# very often. I have used the split and concatenate CLR functions provided by Microsoft though with some sucess but just for one off reports. I would rather use TSQL code that I am more comfortable with in production.

mallick.shiraz SSC Journeyman Points: 76 More actions · Answer 6

Good Article but concatenating rows can be achieved with the help of following as well

DECLARE @FRUITS TABLE

(ID INT,

NAME VARCHAR(50)

)

INSERT INTO @FRUITS VALUES(101, 'BANANA')

INSERT INTO @FRUITS VALUES(102, 'ORANGE')

SELECT * FROM @FRUITS

DECLARE @FNAMES VARCHAR(MAX)

SELECT @FNAMES=ISNULL(@FNAMES,'') + CONVERT(VARCHAR(MAX),[NAME]) + ',' from @FRUITS

SELECT @FNAMES

SET @FNAMES = SUBSTRING(@FNAMES,1,LEN(@FNAMES)-1)

steve-1044999 Valued Member Points: 59 More actions · Answer 7

SELECT CAST(( SELECT CASE WHEN ROW_NUMBER() OVER ( ORDER BY UserName ) > 1

THEN ', '

ELSE ''

END + UserName

FROM Users

ORDER BY UserName

FOR

XML PATH('')

) AS XML).value('.', 'VARCHAR(200)')

This will give you a comma seperated list of values too.

Tom Winter SSC Veteran Points: 296 More actions · Answer 8

As Adam pointed out, if you add ",TYPE" after the FOR XML PATH, you won't need to CAST to XML. TYPE makes FOR XML return its data as an the XML type.

See: TYPE Directive in FOR XML Queries

http://msdn.microsoft.com/en-us/library/ms190025(SQL.90).aspx

Tom Winter SSC Veteran Points: 296 More actions · Answer 9

I like the use of ROW_NUMBER() OVER to get rid of the STUFF. Does anyone have an idea which would be faster?

pjcrout SSChasing Mays Points: 648 More actions · Answer 10

One Word: Pivot.

More Words: A down side of using Pivot is that you have to specify the values to pivot from row values into column names. However, the example provided is the perfect example since the data is stored in a normalized table with a numeric primary key.

This is how I have implemented this in the past. I dynamically building the query in a stored procedure by passing in the a string of numeric values to be included in the query string as the pivot values, and then exec the query string.

The performance has been great with this solution and I have worked with other developers to redeploy else where, however I believe this throws the execution plan out the window 😛

Adam Haines SSC-Insane Points: 23217 More actions · Answer 11

Tom Winter (10/14/2009)
I like the use of ROW_NUMBER() OVER to get rid of the STUFF. Does anyone have an idea which would be faster?

My guess is stuff. Anytime the row_number function is used an sort showplan operator must be used to sort the data accordingly, which means you have more IO and a more expensive query plan.

Note: if you are ordering by the clustered index, the optimizer will not need a sort operator thus allowing you to spare the cost to sort.

My blog: http://jahaines.blogspot.com

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

TheSQLGuru (10/14/2009)
sauraviit, did you actually read the posts before you before making your post?? :w00t:

There are several examples of the 'tsql variable trick' already posted. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 13

Adam Haines (10/14/2009)

Actually you dont have replace the invalid characters if you use the FOR XML Clause, with the TYPE directive. You can then use an xquery to extract the value as it is precieved in the column, without replacing invalid chars.

Now there's a nice little tip. Thanks, Adam.

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rob Fisk SSCommitted Points: 1861 More actions · Answer 14

Actually you dont have replace the invalid characters if you use the FOR XML Clause, with the TYPE directive. You can then use an xquery to extract the value as it is precieved in the column, without replacing invalid chars.

Adam, this is a fantastic tip. I may well keep a permanent types table alongside my tally table.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

Carleton SSCommitted Points: 1706 More actions · Answer 15

I've been happily using the coalesce method for years. However, this link provided by Tom Winter has me converted to the XML method:

Coalesce is not the answer to string concatentation in T-SQL
http://msmvps.com/blogs/robfarley/archive/2007/04/08/coalesce-is-not-the-answer-to-string-concatentation-in-t-sql.aspx

Great dialog here, very useful. -C