CTE

Question

CTE

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

ychen 55059 (4/14/2010)
Thank you so much. That works beautifully.
I'm not familiar with XML. Could you point me to some additional resources so that I can understand what your code actually does?
Thanks again.

You can start here.

http://msdn.microsoft.com/en-us/library/ms190922.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Here is a SQLMagazine article on the topic.

http://www.sqlmag.com/article/tsql3/nifty-ways-to-use-for-xml-path-concatenation.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ychen 55059 SSC-Addicted Points: 433 More actions · Answer 3

ychen 55059

SSC-Addicted

Points: 433

April 14, 2010 at 3:35 pm

#1151788

THANK YOU!!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LutzM SSC Guru Points: 107049 More actions · Answer 5

Maybe this link will be of some help, too:

http://www.sqlservercentral.com/Forums/Topic679572-338-2.aspx

Barry did a great job explaining how the STUFF ... FOR XML PATH actually works.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ychen 55059 SSC-Addicted Points: 433 More actions · Answer 6

Yes, the examples were very helpful indeed. Thank you!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

First, glad we could help.

Second, I knew others would provide you with links that would help you understand the FOR XML. Thanks to you all as well.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Here is another excellent article on Stuff and For XML

http://www.sqlservercentral.com/articles/Test+Data/61572/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

DYNAMICISHWAR SSC Journeyman Points: 92 More actions · Answer 9

I am not sure what the execution times for the following query would be as I have not implemented any hints or indexes on the temp table created however probably you can give this a shot (The only time lag here would actually be the replace statements as mentioned below.... try it out an let me know :w00t: )

select reportid,replace(replace(Val,'<User2>',''),'</User2>','')

from

(select distinct a.reportid,

--(select top 1 User2 from #tbl b where b.reportid=a.reportid) as Test,

(select distinct User2+' ' from #tbl c where c.reportid = a.reportid FOR XML PATH('')) as Val

from #tbl a) x

This would give you the following result:-

rahulraghvn Valued Member Points: 57 More actions · Answer 10

CTE, temp table with index or table variable... which one is more performant?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

rahulraghvn (7/20/2013)
CTE, temp table with index or table variable... which one is more performant?

None of the above. Use the XML method that was posted.

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 12

LutzM (4/14/2010)
Maybe this link will be of some help, too:
http://www.sqlservercentral.com/Forums/Topic679572-338-2.aspx
Barry did a great job explaining how the STUFF ... FOR XML PATH actually works.

SQLRNNR (4/15/2010)
Here is another excellent article on Stuff and For XML
http://www.sqlservercentral.com/articles/Test+Data/61572/

Innocently but blatantly hijacking this thread, it is coincidental that I was recently searching for an article on FOR XML PATH, running across both of these excellent explanations.

However I was looking for a bit more. Specifically, I'm wondering if anyone knows of an article that not only explains how it works and also gives examples that adds the special handling for when the content of the column contains one of the restricted characters? I recently saw someone post a performance comparison of the "standard" approach next to the one with special character handling, and I can't even find that at the moment.

It seems that all of the ones I've run across either show it without special character handling or with, and only Jeff's examines the performance.

If the article explained performance as well it would be a bonus. Thanks.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St