|
Comments
|
|
thats great and helped me a lot to solve a problem
thanks
|
|
|
That's a good piece of syntax I was unaware of. Excellent video.
|
|
|
That's a good piece of syntax I was unaware of. Excellent video.
|
|
|
Nice 1
|
|
Kevin Plotner on
11/10/2008
Very difficult to understand the presenter.
|
|
|
Excelent - powerfull and elegant.
|
|
|
Excelent - powerfull and elegant.
|
|
Douglas Kemp on
11/10/2008
Nice Tip !
|
|
|
I have been looking for an elegant solution like this for quite a while.
Thank you.
|
|
Mohammad Hoque on
11/10/2008
Greate Video
|
|
|
Excellent tip, thank you.
|
|
Munshi Verma on
11/12/2008
Good job!
|
|
Adam Gojdas on
11/24/2008
You may see an issue when the data has "<" and ">" within it. Since FOR XML will entity encode it you may not get what you were expecting. You will see "<" and ">". You will probably want to think about wrapping this with a REPLACE function to catch this.
|
|
Adam Gojdas on
11/24/2008
After reading a comment from here:
http://sqlblog.com/blogs/adam_machanic/archive/2006/10/19/replacing-xp-execresultset-in-sql-server-2005.aspx
I put together this demo for myself:
--Easy way to concatenate multiple rows into a single string
DECLARE @data table(
someData varchar(255) NOT NULL PRIMARY KEY);
INSERT INTO @data (someData) VALUES ('Apple');
INSERT INTO @data (someData) VALUES ('Pear');
INSERT INTO @data (someData) VALUES ('Pineapple');
INSERT INTO @data (someData) VALUES ('Grape');
INSERT INTO @data (someData) VALUES ('<Grape>''"');
--returns -- <Grape>''",Apple, Grape, Pear, Pineapple
SELECT STUFF((
SELECT
mydata.value('/row[1]/x[1]', 'varchar(max)')
FROM
(
SELECT x
FROM
(
SELECT ', ' + d.someData
FROM @data d
FOR XML PATH(''), TYPE
) y (x)
FOR XML RAW, TYPE
) d (mydata)
), 1,2,'');
|
|
Adam Gojdas on
11/24/2008
Sorry for the way the above post came out. It was formatted but it seems to have stripped that all out.
|
|
Plamen Ratchev on
11/25/2008
Adam,
This is very easy to handle. One way is to use the REPLACE function as you noted, but a better method is to change the XML type value to VARCHAR. That will return all original characters. Here is example:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
|
|
Adam Gojdas on
11/25/2008
Plamen, Yes, exactly. The REPLACE method will have it's short comings. I did use a version of the method you mention. That is what the example above I added does. Note the - "mydata.value('/row[1]/x[1]', 'varchar(max)')". Although it does it at a slightly different place than the example you note. Thanks for the other link.
|
|
Plamen Ratchev on
11/26/2008
Adam, correct. I see now you had it in your example, the code wrapped around and I was looking at the end of the query for it.
|
|
|
Sweet. Great video
|
|
|
Excellent
|
|
|
New to SQL server, all the webpages explaining this method were not as clear as this video. Well done and thanks.
|
|
|
Excellant thanks
|
|
|
Great. Thanks a lot.
|
|
|
great tip
|
|
Robert Schmehl on
10/16/2009
need more detail
|
|
|
very good
|
|
Cosmin Tornea on
4/16/2010
cool
|
|
|
very instructive!!!
|
|
|
really useful
|
|
|
I did not know about the Stuff function, thanks
|
|
|
I would like a terse description of the STUFF() function and of other possible uses for FOR XML PATH.
|
|
|
Avg
how to show single comma seprated values in diffrent ros
|
|
|
very cool usage
|
|
|
Short and effective!
|
|
Jamshid Nouri on
11/11/2010
excellent and short demo
|
|
|
Wow !
|
|
Devin Knight on
11/11/2010
Really cool! Didn't know you could do this.
|
|
Tom Hamilton on
11/11/2010
Good clear example - thank you
|
|
|
Really clever! I can use this today. Thans.
|
|
|
This makes me wonder if there is a way to do this in reverse leveraging XML.
|
|
|
taught vey well
|
|
Randall Price on
11/11/2010
Amazing that this can be accomplished with just a single T-SQL statement! Excellent video!
|
|
Steve Harris on
11/11/2010
How would add quotes around each element?
|
|
|
Great Tip! Clear and to the point.
|
|
|
Excellent ..Previously I was using a function and a table to do this . This is simple and easy.
|
|
|
Good Tip!
|
|
|
how can I separate a word in row separated by diff delemeter into diff column
say for example in this I want book, cd, magazine, map, video in diff column what should I do?
should I develop USER DEFINED function?
|
|
Plamen Ratchev on
11/11/2010
Satyabodhi, the STUFF function simply inserts one string into another. Here is detailed description: http://msdn.microsoft.com/en-us/library/ms188043.aspx. In our case it is used just to remove the first comma from the list because it is not needed.
|
|
Plamen Ratchev on
11/11/2010
Steve, to add quotes around each element you just have to concatenate the quotes, the same way the comma is concatenated.
|
|
Plamen Ratchev on
11/11/2010
Kal, here is the best resource of different methods to split a delimited list: http://www.sommarskog.se/arrays-in-sql.html.
|
|
Michael Neymit on
11/11/2010
So simple, yet powerful!
|
|
|
Overwrite two chaacters for ', '
STUFF(<<>> , 1, 2 , '')
|
|
Cedric Walker on
11/12/2010
I just love the simplicity of his teaching. Uses I would never think of. Wonderful.
|
|
|
very good
|
|
|
Very good.
|
|
|
interesting
|
|
Martin Miller on
11/16/2010
A usfeul tip. Thank you.
|
|
|
thanks
|
|
Don Weigend on
11/17/2010
Cool technique!
|
|
|
good
|
|
|
good
|
|
|
Watched the video on Stuff by Andy Warren and this video complimented the Andy Warren video by providing a real-world example of using the Stuff. Good work.
|
|
|
an excellent video
|
|
|
very good
|
|
Charlie Bruno on
4/22/2011
Very interesting topic and presentation.
|
|
Patrick Rios on
4/22/2011
After using the Stuff function, the Results are no longer XML but just a string value.
|
|
|
Awesome!
|
|
Rebecca Lewis on
4/22/2011
Very nice.
|
|
Dennis Allen on
4/22/2011
Great idea! I have a summarization function that I have been using, but this is quite slick. Thanks!
|
|
|
This method of concatenating row values together is MUCH easier than the method I was using. Thx!!!
|
|
Kasey Wheeler on
4/22/2011
Nice video.
|
|
|
This is what I am looking it...
|
|
|
Very Nice, very useful
|
|
Andy Obregon on
4/22/2011
Slick. I have used this for automatic pivoting of normalized tables. I never though of using this to create arrays. Good job!
|
|
|
smart
|
|
|
SQL Share Guys, you should have some sort of "Briefcase" functionality (like on SQL Server Central) so people can save the favorite videos.
|
|
|
-- Another way without XML --
declare @product_list varchar(max)=''
SELECT @product_list = @product_list+','+ product_desc
FROM Products
ORDER BY product_desc
SELECT STUFF(@product_list,1,1,'') AS product_list;
|
|
|
This was great! Thanks.
|
|
|
Very well done video.
|
|
|
content was good but the pronunciation was terrible
|
|
|
Patrick, the purpose of the example is to concatenate to string. If you need the final result as XML you can use CAST.
|
|
Surya Prakash on
4/25/2011
excellent video
|
|
|
Interesting use of FOR XML PATH
|
|
|
Nice pres, but it is important to mention limitations too. This method will result in the XML escaping characters like the ampersand and left and right angle brackets.
|
|
|
Orlando, please see the following article on how to use the same method and avoid XML escaping charater issues: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.asp
|
|
|
Good stuff, I will use this in my programming.
|