Crosstab / Pivot Summary Rows and Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Crosstab / Pivot Summary Rows and Columns

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2009-03-04 : 11:24:57

Hey,

I have been asked to add some summary rows and columns to my cross tab report.

This is some sample code that is used to generate the crosstab at the moment. Please note the user gets to choose the fields and calculation of their report so the following code is generated by my class:

SELECT *

FROM (

SELECT
Selection1_tblTemplate.TemplateCategory + '/' + Selection1_tblTemplate.TemplateType as [ScorecardItem.Template],
Selection1_tblMeasure.MeasureName as [ScoreItem.Measure],
CAST(CAST (YEAR(Selection1_tblScorecard.DateAssessed) as varchar(4)) + ' ' + CAST (DATENAME(mm, Selection1_tblScorecard.DateAssessed) AS varchar(3)) AS nVarchar) as [Column],
CASE Selection1_tblScore.DirectEntry WHEN 2 THEN Selection1_tblScore.ActualScore ELSE (Selection1_tblScore.ActualScore - Selection1_tblMeasure.WorstScore) / (Selection1_tblMeasure.BestScore - Selection1_tblMeasure.WorstScore) * 100 END as CalcField

FROM tblScore as Selection1_tblScore
INNER JOIN tblMeasure as Selection1_tblMeasure ON Selection1_tblMeasure.MeasureID = Selection1_tblScore.MeasureID
INNER JOIN tblScorecard as Selection1_tblScorecard ON Selection1_tblScorecard.ScorecardID = Selection1_tblScore.ScorecardID
INNER JOIN tblTemplate as Selection1_tblTemplate ON Selection1_tblTemplate.TemplateID = Selection1_tblScorecard.TemplateID

WHERE
(1 = 1)
AND
Selection1_tblTemplate.TemplateID IN (4)

) AS s

PIVOT ( AVG(CalcField) FOR [Column] IN ([2006 Jan],[2006 Feb],[2006 Mar],[2006 Apr],[2006 May],[2006 Jun],[2006 Jul],[2006 Aug],[2006 Sep],[2006 Oct],[2006 Nov],[2006 Dec],[2007 Jan],[2007 Feb],[2007 Mar],[2007 Apr],[2007 May],[2007 Jun],[2007 Jul],[2007 Aug],[2007 Sep],[2007 Oct],[2007 Nov],[2007 Dec],[2008 Jan],[2008 Feb],[2008 Mar])) AS p

ORDER BY
[ScorecardItem.Template],
[ScoreItem.Measure]

The summary rows and columns they want to add are AVG, SUM, Best and Worst. Each of these will be optional.

How can I use SQL to generate these as part of the above, is it even possible?

I have considered simply adding these columns when I "draw" the output but this is not possible with the AVG, unless you wanted the AVG of the Averages which they do not want.

Any help is welcomed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 11:28:40

if you want to pivot multiple columns, you need to use this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

leahsmart
Posting Yak Master

133 Posts

Posted - 2009-03-04 : 11:29:53

quote:
Originally posted by visakh16

if you want to pivot multiple columns, you need to use this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

I don't. I want to add summary rows and columns. Did you even read the post?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 12:58:49

quote:
Originally posted by leahsmart

quote:
Originally posted by visakh16

if you want to pivot multiple columns, you need to use this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

I don't. I want to add summary rows and columns. Did you even read the post?

I read it and from what you posted what i could understand was you need to apply multiple aggregates like sum(),avg(),... thats whay i gave you link for multiple crosstabbing.
if this was not what you wanted, either explain your requirement clearly or post info in below format for us to understand better.

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

leahsmart
Posting Yak Master

133 Posts

Posted - 2009-03-05 : 04:40:38

As you can see my crosstab has the months as the columns and template + measure as the rows.

ScorecardItem.Template             ScoreItem.Measure          2006 Jan    2006 Feb    2006 Mar    2006 Apr   
---------------------------------- -------------------------- ----------- ----------- ----------- -----------
Subcontractor/Contract Feedback    Attendances                100         50          68.75       75         
Subcontractor/Contract Feedback    Attitude to Partnering     75          100         65          87.5       
Subcontractor/Contract Feedback    Financial Attitude         25          100         50          87.5       
Subcontractor/Contract Feedback    Health and Safety          NULL        NULL        100         100        
Subcontractor/Contract Feedback    Information                75          75          60          87.5       
Subcontractor/Contract Feedback    Notice for Variations      75          75          50          75         
Subcontractor/Contract Feedback    Overall Performance        50          50          95          87.5       
Subcontractor/Contract Feedback    Payment Record             25          25          75          37.5       
Subcontractor/Contract Feedback    Planning                   50          75          56.25       50         
Subcontractor/Contract Feedback    Quality System             50          75          80          75         
Subcontractor/Contract Feedback    Staff Performance          100         50          75          75         
Subcontractor/Contract Feedback    Valuation of Variations    75          75          65          100        
Subcontractor/Contract Feedback    Waste Reduction            100         0           65          62.5

If the user was to choose to have a row avg it would add one column to the query, this would contain the average for all of those months for each template + measure.

ScorecardItem.Template             ScoreItem.Measure          2006 Jan    2006 Feb    2006 Mar    2006 Apr    AVG
---------------------------------- -------------------------- ----------- ----------- ----------- ----------- ------
Subcontractor/Contract Feedback    Attendances                100         50          68.75       75          50
Subcontractor/Contract Feedback    Attitude to Partnering     75          100         65          87.5        62
Subcontractor/Contract Feedback    Financial Attitude         25          100         50          87.5        91
Subcontractor/Contract Feedback    Health and Safety          NULL        NULL        100         100         10
Subcontractor/Contract Feedback    Information                75          75          60          87.5        50
Subcontractor/Contract Feedback    Notice for Variations      75          75          50          75          66
Subcontractor/Contract Feedback    Overall Performance        50          50          95          87.5        62
Subcontractor/Contract Feedback    Payment Record             25          25          75          37.5        66
Subcontractor/Contract Feedback    Planning                   50          75          56.25       50          37
Subcontractor/Contract Feedback    Quality System             50          75          80          75          68
Subcontractor/Contract Feedback    Staff Performance          100         50          75          75          68
Subcontractor/Contract Feedback    Valuation of Variations    75          75          65          100         83
Subcontractor/Contract Feedback    Waste Reduction            100         0           65          62.5        75

If the user was to choose to have a column avg it would add one row to the query, this would contain the average for all of those templates + measures for each month.

ScorecardItem.Template             ScoreItem.Measure          2006 Jan    2006 Feb    2006 Mar    2006 Apr   
---------------------------------- -------------------------- ----------- ----------- ----------- -----------
Subcontractor/Contract Feedback    Attendances                100         50          68.75       75         
Subcontractor/Contract Feedback    Attitude to Partnering     75          100         65          87.5       
Subcontractor/Contract Feedback    Financial Attitude         25          100         50          87.5       
Subcontractor/Contract Feedback    Health and Safety          NULL        NULL        100         100        
Subcontractor/Contract Feedback    Information                75          75          60          87.5       
Subcontractor/Contract Feedback    Notice for Variations      75          75          50          75         
Subcontractor/Contract Feedback    Overall Performance        50          50          95          87.5       
Subcontractor/Contract Feedback    Payment Record             25          25          75          37.5       
Subcontractor/Contract Feedback    Planning                   50          75          56.25       50         
Subcontractor/Contract Feedback    Quality System             50          75          80          75         
Subcontractor/Contract Feedback    Staff Performance          100         50          75          75         
Subcontractor/Contract Feedback    Valuation of Variations    75          75          65          100        
Subcontractor/Contract Feedback    Waste Reduction            100         0           65          62.5    
AVG                                                           25          25          75          37.5

Please note those average columns just have random numbers in and are not correct.

I hope this clears it up.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources