Max Total In 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

General SQL Server Forums

New to SQL Server Programming

Max Total In Pivot

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-05-22 : 08:15:30

Hi

Want to include a column in a pivot that shows the Maximum value from a pivoted set of values:

select [SalesID], [SalesOpp1] AS SalesOpp1, [SalesOpp2] as SalesOpp2, [SalesOpp3] AS SalesOpp3, [SalesOpp4] AS SalesOpp4
  FROM (
		SELECT [SalesID]
			,[Total] 
			,[Saleops]
		FROM [DB].[owner].[SalesTable]
		) SaleOp
PIVOT (MAX(Total) for SaleOp IN([SalesOpp1], [SalesOpp2], [SalesOpp3], [SalesOpp4])) AS pvt

 SalesID  SalesOpp1  SalesOpp2  SalesOpp3  SalesOpp4  MaximumSalesOpp
          =======  =========  =========  =========  ===============
          0012     100        200        300        300

The bold column is the one I want to add.

Any suggestion on how to do this would be very much appreciated.

Thanks

G

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-22 : 08:25:23

[code]
select [SalesID], [SalesOpp1] AS SalesOpp1, [SalesOpp2] as SalesOpp2, [SalesOpp3] AS SalesOpp3, [SalesOpp4] AS SalesOpp4, MaximumSalesOpp
FROM (
SELECT [SalesID]
,[Total]
,[Saleops]
,[MaximumSalesOpp] = MAX([Total]) OVER (PARTITION BY [SalesID])
FROM [DB].[owner].[SalesTable]
) SaleOp
PIVOT (MAX(Total) for Saleops IN([SalesOpp1], [SalesOpp2], [SalesOpp3], [SalesOpp4])) AS pvt
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-05-23 : 09:01:13

Thanks for that I will put this into my query and see how it goes.

Thanks

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-23 : 15:33:56

if this is for reporting need, you can very easily generate this output format using reporting tool like SSRS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-07-16 : 11:54:51

Tried to get the below code to work but no joy. I included the column as shown below but in never showed up anywhere. Any other ideas?

Thanks

quote:

Originally posted by khtan


select [SalesID], [SalesOpp1] AS SalesOpp1, [SalesOpp2] as SalesOpp2, [SalesOpp3] AS SalesOpp3, [SalesOpp4] AS SalesOpp4, MaximumSalesOpp
FROM (
		SELECT [SalesID]
			,[Total] 
			,[Saleops]
                        ,[MaximumSalesOpp] = MAX([Total]) OVER (PARTITION BY [SalesID])
		FROM [DB].[owner].[SalesTable]
		) SaleOp
PIVOT (MAX(Total) for Saleops IN([SalesOpp1], [SalesOpp2], [SalesOpp3], [SalesOpp4])) AS pvt

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-16 : 21:59:46

show your used code withour which we cant understand what you're doing wrong

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources