Pivot table for Microsoft SQL Server

  • Hi - based on the dates of the other comments, I'm coming to the party a little late.  But thanks for a very clearly-explained (and with two examples) piece of code.  I too have wondered - I've been on SQL since v6.x - how to do a crosstab when the column number is variable.  I'll be interested to see PIVOT and UNPIVOT on 2005.

  • The article was re-released today, so don't take it personal 

    About PIVOT and UNPIVOT in SQL Server 2005; I have more use for UNPIVOT than PIVOT...


    N 56°04'39.16"
    E 12°55'05.25"

  • You could update the table creation to get the datatype of temporary table directly from the source instead of the hard-coded types/sizes.  This would  be easily done with:

    SELECT RowText, ColumnText, Value FROM Source WHERE 1=0 INTO #Aggregates

    Which works because the 1=0 clause insures no rows are matched, but the datatypes are set according to the soruce...

  • 1) Why would I? Then you have no control over the datatype for the aggregated column VALUE (sum).

    2) The syntax is wrong. It should be SELECT .. INTO...FROM ... WHERE ...


    N 56°04'39.16"
    E 12°55'05.25"

  • Thanks for the post.

    Very simple,perfect.Would like to see similar post from you

  • I was just going through pivot example.As you said in pivot you need to hard wire columns. Can we not built a string of column values dynamically for dynamic pivot generation.Please let me know.

    Ref article:


  • Hi Peter,

    Thanks for an excellent article on pivot tables.  I'm not sure if this has been asked before (there is a problem when I clicked on the Next Page link).  In the example that you gave, the CellData column is MONEY type, I need this column to be numeric (9,2).  I tried this but it's showing 1234 instead of 1234.15.  What am I doing wrong here?  Please help.



  • Man, I love that code! I just put it to work and it did the job perfectly!

    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Jimbo Bantog (5/9/2007)

    I need this column to be numeric (9,2). I tried this but it's showing 1234 instead of 1234.15. What am I doing wrong here? Please help.

    It's hard to tell without knowing which modifications you have made.

    N 56°04'39.16"
    E 12°55'05.25"

  • This is a great article. Is it possible to use this to create a view? Thanks!

  • Peter,

    Your code is so well-written and useful. Thank you!

    I would like to add a column at the end for totals and a row at the bottom for totals. Can you steer me in the right direction?

  • Outstanding post! I have been annoyed for years that what we did in MS Access with no effort whatsoever took so much fiddling in SQL Server. Talk about logical absurdity!

    I didn't know enough to re-do using the column index info and create this beautiful solution.

    For those who want to use this: If you just want to copy and paste the thing and use it quickly, you only have to alter the SELECT...FROM...GROUP BY... portion of the posted code and you're running.

    Now THAT is good post, given the nature of this topic especially.

    Thanks an enormous bunch.

  • Thank you!

    It's nice to see someone recognize the simpicity of the algorithm structure.

    You are absolutely correct about the quick start, to only change the insert thingy with SELECT .. GROUP BY portion.

    I also tried to make it easy for future use to add more columns with different kind of aggregations. Just add another portion of dynamic sql and execution.

    N 56°04'39.16"
    E 12°55'05.25"

  • I used your cross tab procedure and it work's beatifully,but how can I put results into table? Thank you

  • INSERT Table1

    EXEC dbo.CrossTabProcedure ...

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 15 posts - 16 through 30 (of 43 total)

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