Pivot table for Microsoft SQL Server

  • So, I have to create Table1 with the fields(values0 I will have?

  • No. You can also have

    SELECT *

    INTO Table1

    FROM #Rows

    at the end of the stored procedure.


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

  • I am a little confused,This is what I have:

    EXECUTE sp_crosstab 'SELECT shipdate FROM tblvwUPS_package_Detail_Daily

    GROUP BY shipdate', 'sum (packages)','service','tblvwUPS_package_Detail_Daily '

    Where do I put :SELECT *

    INTO tblvwUPS_package_Detail_DailyPackages

    FROM #Rows ? Thank you

  • Well... sp_crosstab is not my code so you will have to ask that author.


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

  • Thank you, but I don't know how to do it, can you please help me I would appreciate

  • I too am having the same problem. Creating a sum of a time spent numeric field from a support ticket database. The data shows correctly if you view the #aggregates table. When you run the query for #rows, it removes the decimal point and everything after. ( 214.50 becomes 214). When declaring the datatype as MONEY, the final resultset rounds the value.

    It looks like there is something happening in the exec (@sql) command.

    Great code though...will save a ton of time!

    UPDATE: This line:

    SELECT @SQL = 'ALTER TABLE #ROWS ADD ' + QUOTENAME(@COLUMNTEXT) + ' INT DEFAULT 0'

    is what changes the datatype for the CellData. I've corrected by changing it to:

    SELECT @SQL = 'ALTER TABLE #ROWS ADD ' + QUOTENAME(@COLUMNTEXT) + ' NUMERIC(19,2) DEFAULT 0'

  • Thanks Peter - nice article.

    I have a cross tab reporting project in SSRS 2005, I need to convert the survey responses/answeres and questions text into cross tab format for Example :-

    I have :-

    Survey Type , SurveyName , SurveyCode , QuestionText , Answer/Response , comments these fileds now I need to convert it into :-

    Survey Type , SurveyName , SurveyCode , Question 1 , Answer1 , Question 2 , Answer 2 ------Question 6, Answer 6 , Coments , Average By User.

    Can you please tell me how can I do this by using your code you mentioned in the Pivot Table Article?

  • roopesh.purohit (12/30/2009)


    Thanks Peter - nice article.

    I have a cross tab reporting project in SSRS 2005, I need to convert the survey responses/answeres and questions text into cross tab format for Example :-

    I have :-

    Survey Type , SurveyName , SurveyCode , QuestionText , Answer/Response , comments these fileds now I need to convert it into :-

    Survey Type , SurveyName , SurveyCode , Question 1 , Answer1 , Question 2 , Answer 2 ------Question 6, Answer 6 , Coments , Average By User.

    Can you please tell me how can I do this by using your code you mentioned in the Pivot Table Article?

    If you are using SSRS then column groupings would probably be a better solution for you...



    Clear Sky SQL
    My Blog[/url]

  • Awesome! Thanks! I'm trying to write a report in SSRS and this was exactly what I needed to clean up my failed attempts at using PIVOT. Multiple columns in a PIVOT FTW!

  • A few years too late but thank you for this!

    This is a fantastic work-around and your method of explanation is very easy to understand!

  • Thanks! I have been looking for a way to replicate pivots on our old SQL environments and this works quite nicely =)

  • You're welcome.


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

  • I have been using the standard PIVOT method provided by SQL, and even made a way to handle use case where column names need to be dynamic dates. But now I needed to add a weekly total to the existing daily totals. This template of yours did the trick. I saw that in your example you provided, you show only one insert into the #Aggregates temp table. I modified this first insert statement for my daily totals, then added a second insert statement for the weekly totals. The problem at that point was that the column holding the weekly total was appearing in position 2, whereas I wanted it to be the right most column. e.g. [Widgets], [day1Total], [day2Total],...,[WeeklyTotal]. To solve this, I modified the temp table and insert statements by adding an AggregateSetID column. The daily total insert statement is hard coded to insert a 1 in this column, and the weekly totals are hard coded to insert a 2. Then, when I go to get the ColumnText from #Aggregates toward the end of the procedure, I sort first by AggregateSetID, then by ColumnText. This puts my columns in the right order, i.e. Widgets, then daily totals, then weekly total

    I also had to update the NOLOCK hints by adding the keyword WITH e.g. FROM #Aggregates WITH (nolock, INDEX(IX_Aggregates));

    SQL 2012

  • phillipacevedo - Friday, March 30, 2018 1:36 PM

    I have been using the standard PIVOT method provided by SQL, and even made a way to handle use case where column names need to be dynamic dates. But now I needed to add a weekly total to the existing daily totals. This template of yours did the trick. I saw that in your example you provided, you show only one insert into the #Aggregates temp table. I modified this first insert statement for my daily totals, then added a second insert statement for the weekly totals. The problem at that point was that the column holding the weekly total was appearing in position 2, whereas I wanted it to be the right most column. e.g. [Widgets], [day1Total], [day2Total],...,[WeeklyTotal]. To solve this, I modified the temp table and insert statements by adding an AggregateSetID column. The daily total insert statement is hard coded to insert a 1 in this column, and the weekly totals are hard coded to insert a 2. Then, when I go to get the ColumnText from #Aggregates toward the end of the procedure, I sort first by AggregateSetID, then by ColumnText. This puts my columns in the right order, i.e. Widgets, then daily totals, then weekly total

    I also had to update the NOLOCK hints by adding the keyword WITH e.g. FROM #Aggregates WITH (nolock, INDEX(IX_Aggregates));

    SQL 2012

    This article was written a long time ago and I'd advise against its use. Using a dynamic cross tab query will be much faster (when I changed a code like this it became 4 times faster). Read the following articles to get a better option.
    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - SQLServerCentral
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - SQLServerCentral

    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

Viewing 14 posts - 31 through 43 (of 43 total)

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