how to get Pivot result set using 3 tables

Question

how to get Pivot result set using 3 tables

Viewing 7 posts - 31 through 36 (of 36 total)

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

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 1

Erland Sommarskog wrote:

Jason A. Long wrote:
3. What would cause or allow SQL Server to ignore an explicit ORDER BY clause when a specific row goal has been established?
How would an ORDER BY or TOP guarantee that all values will be added to @sql? SQL Server may still opt to only assign the variable once. It may not be likely, but as I said there are no guarantees.
Yes, I agree that the FOR XML syntax is ugly and in no way intuitive, and because of the way you have to use it, it may not always give the best performance. But it is guaranteed to produce the expected result.

Erland, My apologies. I don't mean to keep beating a dead horse here. I have no doubt that you are 100% correct. I am simply trying (for my own edification) to work out a scenario where the array order would/could possibly diverge from the explicit ORDER BY clause.

Thinking through the problem... For the array to be miss-ordered, the Compute Scalar operation would have to precede the Top N Sort operation in the execution plan. Based on the nature of the query, that shouldn't be possible, at least not without ignoring the row goal created by the TOP(N).

The only other scenario that presents as a possibility, is parallelism. At least in theory, I can see where it could be possible that separate threads compute separate arrays before they are concatenated after the Gather Streams node. In which case, the (MAXDOP 1) hint should be added.

I am open to the idea that I'm simply thinking about this incorrectly.

Edit: When you say, "SQL Server may still opt to only assign the variable once.", are you indicating that SQL Server may not only get the order wrong but also not include all elements (assuming there are more than one) in the array?

This reply was modified 4 years, 3 months ago by Jason A. Long.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

Jason A. Long wrote:

Edit: When you say, "SQL Server may still opt to only assign the variable once.", are you indicating that SQL Server may not only get the order wrong but also not include all elements (assuming there are more than one) in the array?

Exactly. Such cases have appeared through the years, but I don't have any form of repro on any recent version.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 3

gaurav wrote:

Thank you so much for your help.
Sir, now I have added " group by ast.asset_name" and " LEFT JOIN ctePreAgg " it works fine.
Thank you, thanks a lot 😀

Good. But don't go away, yet. You're the one that will have to maintain it and we needed to get this working before we can talk about it bit and make one more change to make it a whole lot more bullet proof. First and like I previously asked, what version of SQL Server are you using for this?

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

Jeff, he has already answered which version he has: SQL Express 2012 SP1. (So, Gaurav, you should apply the latest Service Pack for SQL 2012, which is SP4!)

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 5

Erland Sommarskog wrote:

Jason A. Long wrote:
Edit: When you say, "SQL Server may still opt to only assign the variable once.", are you indicating that SQL Server may not only get the order wrong but also not include all elements (assuming there are more than one) in the array?
Exactly. Such cases have appeared through the years, but I don't have any form of repro on any recent version.

Thank you Erland. Now that we're talking about it, I have actually seen this behavior myself. I wish I could remember the exact syntax/circumstances needed to reproduce it. I will do some digging... IIRC that was precisely the reason for adding the TOP(N) row goal in the first place. It prevented SQL Server from thinking that it could avoid reading all but the "last row".

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 6

Erland Sommarskog wrote:

Jeff, he has already answered which version he has: SQL Express 2012 SP1. (So, Gaurav, you should apply the latest Service Pack for SQL 2012, which is SP4!)

Thanks, Erland... I missed it. I'll be back. I'm starting to think you might have been right, though. I showed the Op the "easy way for understanding" with the intention of showing him how to convert that using for XML PATH but he's not returned. I'll do the conversion and post it tonight or tomorrow in case others read this.

Next time, I'll take your good advice and just do it right from the git instead of trying to teach by revvelation because it sure didn't work out as planned.

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 7

Jeff Moden wrote:

Thanks, Erland... I missed it. I'll be back. I'm starting to think you might have been right, though. I showed the Op the "easy way for understanding" with the intention of showing him how to convert that using for XML PATH but he's not returned.

It was weird. The thread produced over 30 posts in one day. The next day - nothing!

As for the original poster, maybe he eventually listened to our original advice and did the pivoting elsewhere.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]