CODEWITHSUNDEEP
sqlsql-serverpivotpercentage
Mike Mirabelli
Mike Mirabelli

Reputation: 410

SQL-Using PIVOT with a UNION?

I have this query (Sql-srv 2012 SE):

SELECT Dimension,
    [Revenues],
    [Cost of Sales],
    [ GrossProfit]
FROM
   (select isnull((CASE WHEN BAA.ACC_0 IN ('41100','42000','45100','42200','42300','42400','45200','45205','41850','48100','41150','41600'
   , '46100','44100' )
Then 'Revenues' Else 'Cost of Sales' End) , ' GrossProfit') as Type,

 ISNULL(REPLACE(LTRIM(RTRIM(CONCAT(CCE1_0, CCE6_0, CCE7_0))),'NONE',''), '[Total]') as Dimension,

SUM(( BAA.DEBLED_1 - BAA.CDTLED_1) +
( BAA.DEBLED_2 - BAA.CDTLED_2) +
( BAA.DEBLED_3 - BAA.CDTLED_3) +
( BAA.DEBLED_4 - BAA.CDTLED_4) +
( BAA.DEBLED_5 - BAA.CDTLED_5) +
(BAA.DEBLED_6 - BAA.CDTLED_6) + 
( BAA.DEBLED_7 - BAA.CDTLED_7) +
( BAA.DEBLED_8 - BAA.CDTLED_8)+
( BAA.DEBLED_9 - BAA.CDTLED_9)+
( BAA.DEBLED_10 - BAA.CDTLED_10)+
(BAA.DEBLED_11 - BAA.CDTLED_11)+
( BAA.DEBLED_12 - BAA.CDTLED_12)) * (-1) as Amount

 from x3v6.CICPROD.BALANA BAA where BAA.FIY_0 = RIGHT(YEAR(CAST(GETDATE() as DATE)),2)
and BAA.ACC_0 IN (
'49100','51100','52100','52200','52300','52400','52750','42100','53100','57100','70800','57200','57300',
'57400','57500','58100','58200','58300','58400','59100','59200','70240','81300','41100','42000','45100',
'42200','42300','42400','45200','45205','41850','48100','41150','41600', '46100','44100' ) and BAA.BPR_0 = '' and BAA.FCY_0 = 'OAK'

Group by Grouping Sets ((CASE WHEN BAA.ACC_0 IN ('41100','42000','45100','42200','42300','42400','45200','45205','41850','48100','41150','41600'
, '46100','44100' ) Then 'Revenues' Else 'Cost of Sales' End) ,()) , GROUPING SETS((REPLACE(LTRIM(RTRIM(CONCAT(CCE1_0, CCE6_0, CCE7_0))),'NONE','')) , ()) ) as a1

PIVOT
 (SUM(Amount)
FOR Type 
    IN ( [Revenues], [Cost of Sales], [ GrossProfit])
) AS b

which returns perfectly what I wanted to achieve which is this:

>

 >    Dimension  Revenues     Cost of Sales       GrossProfit
>           0.00000000000         174034.31000000000    174034.31000000000 
  BLENDING  2811969.85000000000 -1862626.99000000000    949342.86000000000
> COCOA     5746483.14000000000 -3877530.22000000000    1868952.92000000000
> COPACK    1904620.24000000000 -1255488.25000000000    649131.99000000000
> DSM      11530715.99000000000 -9598145.67000000000    1932570.32000000000
> EVERLAST  432385.42000000000  -392729.54000000000     39655.88000000000
> IFF       5677916.64000000000 -4879025.50000000000    798891.14000000000
> KW        914780.08000000000  -778366.84000000000     136413.24000000000
> KW-COCOA  1102257.81000000000 -527290.76000000000     574967.05000000000
> KWBL      506291.57000000000  -295441.61000000000     210849.96000000000
> LALLEMAND 533787.34000000000  -518768.64000000000     15018.70000000000
> RM-RESALE 1807209.70000000000 -1627262.93000000000    179946.77000000000
> ROQ       2804133.54000000000 -2248721.60000000000    555411.94000000000
> TLBL      1794480.19000000000 -1468205.23000000000    326274.96000000000
> TLCI      6956307.61000000000 -5141794.87000000000    1814512.74000000000
> TLCIS     813840.98000000000  -693984.47000000000     119856.51000000000
> TOLLBLND  364579.49000000000  -217778.07000000000     146801.42000000000
> WRH       NULL                 2080.97000000000        2080.97000000000
> [Total]   45701759.59000000000-35207045.91000000000   10494713.68000000000

I tried to use a UNION clause to add 'Gross Profit Percentage' (basically the column GROSSPROFIT / REVENUES * 100, but I don't know if it works with a pivot. What would I have to do to achieve this and does anyone have luck with this?

Upvotes: 0

Views: 454

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

SELECT Dimension,
    [Revenues],
    [Cost of Sales],
    [ GrossProfit],
    [ GrossProfit]/ nullif([Revenues],0) * 100 as [Gross Profit Percentage]
    .
    .
    .

Upvotes: 1

Mike Mirabelli
Mike Mirabelli

Reputation: 410

>    Dimension  Revenues     Cost of Sales       GrossProfit                    Profit %
>           0.00000000000         174034.31000000000    174034.31000000000       0
  BLENDING  2811969.85000000000 -1862626.99000000000    949342.86000000000      33.76% 
> COCOA     5746483.14000000000 -3877530.22000000000    1868952.92000000000     etc..
> COPACK    1904620.24000000000 -1255488.25000000000    649131.99000000000
> DSM      11530715.99000000000 -9598145.67000000000    1932570.32000000000
> EVERLAST  432385.42000000000  -392729.54000000000     39655.88000000000
> IFF       5677916.64000000000 -4879025.50000000000    798891.14000000000
> KW        914780.08000000000  -778366.84000000000     136413.24000000000
> KW-COCOA  1102257.81000000000 -527290.76000000000     574967.05000000000
> KWBL      506291.57000000000  -295441.61000000000     210849.96000000000
> LALLEMAND 533787.34000000000  -518768.64000000000     15018.70000000000
> RM-RESALE 1807209.70000000000 -1627262.93000000000    179946.77000000000
> ROQ       2804133.54000000000 -2248721.60000000000    555411.94000000000
> TLBL      1794480.19000000000 -1468205.23000000000    326274.96000000000
> TLCI      6956307.61000000000 -5141794.87000000000    1814512.74000000000
> TLCIS     813840.98000000000  -693984.47000000000     119856.51000000000
> TOLLBLND  364579.49000000000  -217778.07000000000     146801.42000000000
> WRH       NULL                 2080.97000000000        2080.97000000000
> [Total]   45701759.59000000000-35207045.91000000000   10494713.68000000000

This is how I would want to display the results.

Upvotes: 0

Related Questions