Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Pivot table for Microsoft SQL Server Expand / Collapse
Author
Message
Posted Monday, November 10, 2008 12:45 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 11:58 AM
Points: 1,279, Visits: 1,886
So, I have to create Table1 with the fields(values0 I will have?
Post #600136
Posted Monday, November 10, 2008 12:52 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:58 AM
Points: 2,397, Visits: 3,407
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"
Post #600143
Posted Monday, November 10, 2008 1:05 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 11:58 AM
Points: 1,279, Visits: 1,886
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
Post #600152
Posted Monday, November 10, 2008 1:13 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:58 AM
Points: 2,397, Visits: 3,407
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"
Post #600156
Posted Monday, November 10, 2008 1:22 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 11:58 AM
Points: 1,279, Visits: 1,886
Thank you, but I don't know how to do it, can you please help me I would appreciate
Post #600166
Posted Sunday, February 22, 2009 12:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 6, 2010 10:17 PM
Points: 1, Visits: 23
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'


Post #662145
Posted Wednesday, December 30, 2009 3:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 28, 2010 5:23 AM
Points: 2, Visits: 19
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?
Post #840235
Posted Wednesday, December 30, 2009 3:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,949, Visits: 8,318
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
Kent user group
Post #840237
Posted Friday, May 28, 2010 1:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 28, 2010 1:42 PM
Points: 1, Visits: 2
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!
Post #929894
Posted Friday, January 13, 2012 12:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 2, 2012 11:53 PM
Points: 4, Visits: 4
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!
Post #1235402
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse