Pivot? - SQL Server Forums

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)

Pivot?

Author

Topic

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2011-07-01 : 18:44:11

I need some help with this one. I have the pivot query all set up, but what I need to do now is have a grand total avg for both rows and columns.

Anyone have an idea how to achieve this?? Here's my query without the grand avg for reference.


create table #temp (Area varchar(50), AuditWeek smalldatetime, Score float)

Insert into #temp (Area, Score, AuditWeek)
SELECT     sh.Area, sd.Score, Convert(varchar(8), sc.AuditWeek, 101)
FROM         dbo.TAM_Safety_Detail AS sd INNER JOIN
                      dbo.TAM_Safety_Calendar AS sc ON sd.CalendarRowID = sc.RowID INNER JOIN
                      dbo.TAM_Safety_Hdr AS sh ON sd.HdrRowID = sh.ParentRowID

Select * from #temp
pivot (Sum(Score) for Area in ([Lg Press Room 200], [Sm Press Room],[201 Press Rm], 
[Welding], [Tapping], [Quality Labs], [200 ToolRm], [201 ToolRm], [Maint], [Steel Receiving and Storage], 
[Shipping Receiving & Storage 200], [Shipping Receiving & Storage 201],
[Assembly Shipping Receiving & Storage  800], [Outside storage behind 200 & 201], 
[Press 42 - Pit Area], [Globe Tech Machining, Die Build & Press Area], [Globe Tech Press Area])) as ScorePerDept

Drop table #temp

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2011-07-06 : 08:01:16

Is this the impossible dream??

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-06 : 09:48:31

It's possible, but I don't think you can do it with PIVOT:

CREATE TABLE #temp (Area VARCHAR(50), AuditWeek SMALLDATETIME, Score FLOAT)

INSERT INTO #temp (Area, Score, AuditWeek)
SELECT sh.Area, sd.Score, CONVERT(VARCHAR(8), sc.AuditWeek, 101)
FROM  dbo.TAM_Safety_Detail AS sd 
INNER JOIN dbo.TAM_Safety_Calendar AS sc ON sd.CalendarRowID = sc.RowID 
INNER JOIN dbo.TAM_Safety_Hdr AS sh ON sd.HdrRowID = sh.ParentRowID

SELECT IsNull(AuditWeek,'Grand Total') AuditWeek,
SUM(CASE WHEN Area='Lg Press Room 200' THEN Score END) [Lg Press Room 200],
SUM(CASE WHEN Area='Sm Press Room' THEN Score END) [Sm Press Room],
SUM(CASE WHEN Area='201 Press Rm' THEN Score END) [201 Press Rm],
SUM(CASE WHEN Area='Welding' THEN Score END) [Welding],
SUM(CASE WHEN Area='Tapping' THEN Score END) [Tapping],
SUM(CASE WHEN Area='Quality Labs' THEN Score END) [Quality Labs],
SUM(CASE WHEN Area='200 ToolRm' THEN Score END) [200 ToolRm],
SUM(CASE WHEN Area='201 ToolRm' THEN Score END) [201 ToolRm],
SUM(CASE WHEN Area='Maint' THEN Score END) [Maint],
SUM(CASE WHEN Area='Steel Receiving AND Storage' THEN Score END) [Steel Receiving AND Storage],
SUM(CASE WHEN Area='Shipping Receiving & Storage 200' THEN Score END) [Shipping Receiving & Storage 200],
SUM(CASE WHEN Area='Shipping Receiving & Storage 201' THEN Score END) [Shipping Receiving & Storage 201],
SUM(CASE WHEN Area='Assembly Shipping Receiving & Storage  800' THEN Score END) [Assembly Shipping Receiving & Storage  800],
SUM(CASE WHEN Area='Outside storage behind 200 & 201' THEN Score END) [Outside storage behind 200 & 201],
SUM(CASE WHEN Area='Press 42 - Pit Area' THEN Score END) [Press 42 - Pit Area],
SUM(CASE WHEN Area='Globe Tech Machining' THEN Score END) [Globe Tech Machining],
SUM(CASE WHEN Area='Die Build & Press Area' THEN Score END) [Die Build & Press Area],
SUM(CASE WHEN Area='Globe Tech Press Area' THEN Score END) [Globe Tech Press Area],
SUM(Score) AS TotalScore
FROM #temp
GROUP BY AuditWeek WITH ROLLUP

DROP TABLE #temp

Be advised that these kinds of totals and pivots are best done in a reporting package rather than using SQL to generate them.

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2011-07-06 : 11:02:32

Nice! Thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources