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