Dynamic Pivot, Passing Temp Table Variables to Dynamic SQL

  • Jambu Krishnamurthy

    SSCrazy

    Points: 2054

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

  • skosanabv

    Valued Member

    Points: 66

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

  • Felix Pamittan

    Mr or Mrs. 500

    Points: 535

    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

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

  • Jambu Krishnamurthy

    SSCrazy

    Points: 2054

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

    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

    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

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

    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

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

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

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Lisa Slater Nicholls

    Hall of Fame

    Points: 3311

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

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

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