Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pivot table for Microsoft SQL Server


Pivot table for Microsoft SQL Server

Author
Message
Krasavita
Krasavita
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1283 Visits: 1894
So, I have to create Table1 with the fields(values0 I will have?
SwePeso
SwePeso
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 3431
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"
Krasavita
Krasavita
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1283 Visits: 1894
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
SwePeso
SwePeso
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 3431
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"
Krasavita
Krasavita
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1283 Visits: 1894
Thank you, but I don't know how to do it, can you please help me I would appreciate
cbrooks-501370
cbrooks-501370
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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'
roopesh.purohit
roopesh.purohit
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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?
Dave Ballantyne
Dave Ballantyne
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1818 Visits: 8370
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
mdevenney
mdevenney
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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!
nmonroe
nmonroe
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search