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 Tuesday, June 13, 2006 12:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:07 PM
Points: 2,382, Visits: 3,369

You're welcome!

Now I have to look up "arduous" in my dictionary...




N 56°04'39.16"
E 12°55'05.25"
Post #286906
Posted Tuesday, June 13, 2006 2:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 21, 2013 7:39 AM
Points: 53, Visits: 41

Thanks Peter.  It is very good procedure and I shall use it.

R. M. Joseph

Post #286923
Posted Wednesday, June 21, 2006 7:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 16, 2012 3:50 AM
Points: 37, Visits: 103
You got my Excellent! I've seen quite a few procedures, but I really appreciate your methodology: you paved a complete road from the ground up with concise and adequate explanations. I shall use it as a solid base for my pivot table reports. Thanks a lot.
 
Silvio E. Costa
Post #289038
Posted Monday, July 17, 2006 2:14 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:07 PM
Points: 2,382, Visits: 3,369

You are welcome!

I am satisfied you liked the article. I really struggled to keep it simple and consise. And as you write, my purpose was to give away the base of what pivot tables really are.




N 56°04'39.16"
E 12°55'05.25"
Post #294984
Posted Tuesday, March 27, 2007 1:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 23, 2009 12:11 PM
Points: 1, Visits: 7

Fantastic Article,

I now need to somehow work out to add ordering based on these "unknown" columns but this is a great start to stuff I really did not understand.

A Huge help - thanks.

Post #354308
Posted Friday, March 30, 2007 2:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 26, 2010 10:00 AM
Points: 92, Visits: 47
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.
Post #354988
Posted Friday, March 30, 2007 3:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:07 PM
Points: 2,382, Visits: 3,369

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"
Post #354994
Posted Tuesday, April 03, 2007 5:39 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, February 07, 2014 12:44 PM
Points: 98, Visits: 10

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...




Post #355787
Posted Tuesday, April 03, 2007 10:51 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:07 PM
Points: 2,382, Visits: 3,369

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"
Post #355823
Posted Thursday, April 05, 2007 12:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 12, 2009 7:39 PM
Points: 7, Visits: 14

Thanks for the post.

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

Post #356163
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse