Pivot the Table and get the sum

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 2008 Forums

Transact-SQL (2008)

Pivot the Table and get the sum

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2009-03-23 : 08:18:07

Hi,
I want the table to be PIVOT and get the sum both row wise and column wise.

DECLARE @Sample table
(
VHWSCode VARCHAR(50),
[Month] VARCHAR(20),
TotalHours VARCHAR(12)
)

INSERT @Sample

SELECT 'WE04', 'March', '52:22:00' UNION ALL
SELECT 'WE04', 'APRIL', '62:18:00' UNION ALL
SELECT 'WE05', 'March', '45:40:00' UNION ALL
SELECT 'WE05', 'APRIL', '70:16:00'

This is my input data

I want Output in the format of

VHWSCode Jan Feb Mar Apr .Dec Total
WE04 52:22:00 62:18:00 114.40
WE05 45:40:00 70:16:00 115.56

Total 98.02. 132.34

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-24 : 12:49:31

here is sample:
SELECT VHWSCode,
Jan = Max(case when [Month] ='Jan' then TotalHours end) ,
Apr= Max(case when [Month] ='April' then TotalHours end),
Mar= Max(case when [Month] ='March' then TotalHours end )
FROM Sampletable
GROUP BY VHWSCode

Jurgen.Asselman
Starting Member

1 Post

Posted - 2009-04-21 : 03:31:39

SELECT VHWSCode , [March] as [MARCH] , [APRIL] as [APRIL]
FROM ( SELECT VHWSCode , [Month], TotalHours FROM @Sample ) As T
PIVOT
( MAX(TotalHours) FOR [Month] IN ([March], [APRIL])) As pvt
ORDER BY pvt.VHWSCode

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-21 : 03:52:33

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources