November 10, 2008 at 12:45 pm
So, I have to create Table1 with the fields(values0 I will have?
November 10, 2008 at 12:52 pm
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"
November 10, 2008 at 1:05 pm
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
November 10, 2008 at 1:13 pm
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"
November 10, 2008 at 1:22 pm
Thank you, but I don't know how to do it, can you please help me I would appreciate
February 22, 2009 at 12:14 am
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'
December 30, 2009 at 3:06 am
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?
December 30, 2009 at 3:22 am
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...
May 28, 2010 at 1:43 pm
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!
January 13, 2012 at 12:32 am
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!
August 1, 2012 at 4:48 am
Thanks! I have been looking for a way to replicate pivots on our old SQL environments and this works quite nicely =)
August 1, 2012 at 4:57 am
You're welcome.
N 56°04'39.16"
E 12°55'05.25"
March 30, 2018 at 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
April 2, 2018 at 6:26 am
phillipacevedo - Friday, March 30, 2018 1:36 PMI 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 totalI 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
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply