Dynamic Pivot, Passing Temp Table Variables to Dynamic SQL

Question

Post reply

Dynamic Pivot, Passing Temp Table Variables to Dynamic SQL

Jambu Krishnamurthy

SSCrazy

Points: 2054
More actions
October 21, 2015 at 10:13 pm

#309223

Comments posted to this topic are about the item Dynamic Pivot, Passing Temp Table Variables to Dynamic SQL

Viewing 13 posts - 1 through 12 (of 12 total)

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

skosanabv Valued Member Points: 66 More actions · Answer 1

Nice one, this is quite useful in many situations. Thanks.:-)

Felix Pamittan Mr or Mrs. 500 Points: 535 More actions · Answer 2

Nice article. Although I still prefer Jeff Moden's dynamic crosstab approach:

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql =

'SELECT

TableName' + CHAR(10)

SELECT @sql = @sql +

', AVG(CASE WHEN YEAR(CreatedDate) = ' + CONVERT(NVARCHAR(4), yr) + ' THEN RecordCount END) AS ' + QUOTENAME(yr) + CHAR(10)

FROM (

SELECT DISTINCT YEAR(CreatedDate) AS yr FROM @TempTable

) t

SELECT @sql = @sql +

'FROM @TempTable

GROUP BY TableName

ORDER BY TableName'

EXEC sp_executesql @sql, N'@TempTable dbo.TableType READONLY', @TempTable;

The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 3

The task I have is to identify where in my daily sql I can use this.

Jambu Krishnamurthy SSCrazy Points: 2054 More actions · Answer 4

Hi Iwas,

Most analytics/transformation tasks will require

these kind

of twisty sql/tsql code. As commented by

One of the readers above, it is apparent that this

is only one of the ways. I saw the cross tab solution

and that makes great sense too and looks more

elegant.

Sql/tsql was not meant for these kinds of tasks

And so newer and newer features are being added

And I happened to use this in one of my recent projects.

So shared it, thanks SSC.

Thanks for your comment!

Jambu

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Is great to know that you're sharing your real life experience. As commented above, the cross tabs approach has been way back before pivot and is more flexible. That doesn't means that you didn't do a very good effort and showed what's needed when using a table variable (even if most people like me won't advocate their use).

I'd like to encourage you to use ORDER BY in your queries even if DISTINCT is used (which needs a sort by itself) to define real order of your columns. I'll also suggest that you use the FOR XML PATH concatenation approach[/url] which allows for a more secure and flexible order of the columns. I've seen that the columns in a pivot get messed up because the ORDER BY was ignored in the concatenation method you used.

This is the approach I usually follow for dynamic pivots.

declare @sql nvarchar(max)

SELECT @sql = 'SELECT TableName ' + CHAR( 13)

+ (SELECT CHAR(9) + ', AVG( CASE WHEN YEAR( CreatedDate) = ' + calyear + ' THEN RecordCount END) AS ' + QUOTENAME(calyear) + CHAR(13)

FROM @TempTable t

CROSS APPLY (SELECT CONVERT( CHAR(4),YEAR( CreatedDate))) y( calyear)

GROUP BY calyear

ORDER BY calyear

FOR XML PATH(''),TYPE).value('.', 'nvarchar(max)')

+ CHAR(9) + ',AVG( RecordCount) AS Total ' + CHAR( 13)

+ 'FROM @TempTable ' + CHAR( 13)

+ 'GROUP BY TableName ' + CHAR( 13)

+ 'ORDER BY TableName; '

exec sp_executesql @sql,N'@TempTable dbo.TableType READONLY', @TempTable;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jambu Krishnamurthy SSCrazy Points: 2054 More actions · Answer 6

Thanks Luis,

Your explanation and approach makes lot of sense. And the next time I come across such situations I will use the suggested approaches. Looks like cross tab is the best way to go from what I see in the feedbacks. I wish I knew that earlier.

Thanks again for the exhaustive feedback.

Jambu

farfel SSC Veteran Points: 235 More actions · Answer 7

I have searched the internet for this situation to no avail, but we believe that starting in some version of SQL2008+ that if you define the @columns variable as anything other than nvarchar(max) that the sql will fail intermittently.

Of course this means the @sql variable must be nvarchar(max) also.

It was a lot of trial and error to accidentally encounter a solution. We had several dynamically pivoting stored procedure that executed in jobs daily for years with no issues. then suddenly one day they intermittently failed. Modifying the variable name in the stored procedure occasionally would fix the issue, but only temporarily. It would run for a while and then fail.

Once we set the variables to nvarchar(Max) - no more failures.

Perhaps someone else has some insight or extra details to share about this issue.

Here are more particulars:

The contained a variable @SCols varchar(1000)

This is the error that would report intermittently "Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) An expression of non-boolean type specified in a context where a condition is expected, near 'S'. [SQLSTATE 42000] (Error 4145)."

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

farfel (10/22/2015)
I have searched the internet for this situation to no avail, but we believe that starting in some version of SQL2008+ that if you define the @columns variable as anything other than nvarchar(max) that the sql will fail intermittently.
Of course this means the @sql variable must be nvarchar(max) also.
It was a lot of trial and error to accidentally encounter a solution. We had several dynamically pivoting stored procedure that executed in jobs daily for years with no issues. then suddenly one day they intermittently failed. Modifying the variable name in the stored procedure occasionally would fix the issue, but only temporarily. It would run for a while and then fail.
Once we set the variables to nvarchar(Max) - no more failures.
Perhaps someone else has some insight or extra details to share about this issue.
Here are more particulars:
The contained a variable @SCols varchar(1000)
This is the error that would report intermittently "Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) An expression of non-boolean type specified in a context where a condition is expected, near 'S'. [SQLSTATE 42000] (Error 4145)."

If the @SCols is declared as varchar(1000), when concatenating with the rest of the sql statement it will implicitly cast the resultant string into a varchar(1000) before casting it to a nvarchar(max) to assign it to a variable. I hope that this makes sense.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

farfel SSC Veteran Points: 235 More actions · Answer 9

Yes it does, thank you. Regardless though, we have found it best practice to make those variables nvarchar(max). Lazy, but effective.:-)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

farfel (10/22/2015)
Yes it does, thank you. Regardless though, we have found it best practice to make those variables nvarchar(max). Lazy, but effective.:-)

I wouldn't call it lazy if it's the right thing to do. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

Iwas Bornready - Thursday, October 22, 2015 7:44 AM
The task I have is to identify where in my daily sql I can use this.

High performance reporting is the normal usage. It can also be used to make "records" that have been split by column into an EAV table (such as you might find for audit tables or "flexible" user tables) back into whole "records".

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

Lisa Slater Nicholls Hall of Fame Points: 3455 More actions · Answer 12

farfel - Thursday, October 22, 2015 9:28 AM
I have searched the internet for this situation to no avail, but we believe that starting in some version of SQL2008+ that if you define the @columns variable as anything other than nvarchar(max) that the sql will fail intermittently.Of course this means the @sql variable must be nvarchar(max) also.

I have not seen this happen; thank you for mentioning it, I'll do some experiments.
My article about this technique (written in 2007) is here:
http://spacefold.com/lisa/post/2007/08/15/Matrix-Rebuilt-More-non-standard-fun-with-T-SQL.aspx
If folks are enjoying this technique, they might find it useful to see a different explanation.
I used a WHILE loop instead of a COALESCE(...) in which I determine two parts of the eventual SQL statement separately, to allow me to add generated column aliases for the result -- under the circumstances (the number of iterations is the number of columns) it seemed cleaner and more expressive, and no real perf difference.
Of course now I have to try it Jeff's way <sigh>.