CODEWITHSUNDEEP
sql-serverpivot
Srinivas V.
Srinivas V.

Reputation: 321

Multi pivot in sql server

the following is my sql server's query:

     SELECT t.type ,
        t.fmsales ,
        t.indussales 
 FROM   ( SELECT    CASE WHEN ( GROUPING(mti.type) = 1 ) THEN 'ALL'
                         ELSE ISNULL(mti.Type, 'UNKNOWN')
                    END AS type ,
                    SUM(ISNULL(mti.SALES_THIS + mti.SWITCHIN_THIS, 0)) fmsales ,
                    SUM(ISNULL(mti.SALES_ALL + mti.SWITCHIN_ALL, 0)) indussales 

          FROM      dbo.IMonthly_trigger_Industry mti ,
                    dbo.RMMappingDb rm ,
                    dbo.EmployeeDB ed
          WHERE     ed.EmployeeCode = 1217
                    AND rm.BranchName = mti.Branch
                    AND rm.ARNCode = mti.BROKER
                    AND rm.EmployeeCode = ed.EmployeeCode
                    AND mti.BROKER_NAME LIKE '%Pvt Limited%'
          GROUP BY  mti.Type
                    WITH CUBE
        ) t
 WHERE  t.type IS NOT NULL
 GROUP BY t.type ,
        t.fmsales ,
        t.indussales;

and the result of this is as like this:

enter image description here

I want to transpose this result set, so that rows become columns and columns become rows. I earlier used the pivot method where I have only one column to do the aggregations, but here it is different. Can some one help me out in achieving this.

The result should be like this:

enter image description here

How can I achieve this ? I tried using the pivot, since pivot allows aggregate on only one column I couldn't get the result as expected. Any help is much appreciated. Thanks in advance.

Upvotes: 1

Views: 82

Answers (4)

mohan111
mohan111

Reputation: 8865

we can do it in cross apply but it's late 

DECLARE @t TABLE(type NVARCHAR(MAX), fmsales MONEY, indussales MONEY)

    INSERT INTO @t VALUES
    ('ALL', 0, 82.1),
    ('CASH', 0, 0),
    ('DEBT', 0, 62),
    ('EQUITY', 0, 20.1)

    select  col As Products,[ALL],[Cash],[Debt],[Equity],
    from (select type,col,val from @t

    CROSS APPLY (values(fmsales, 'fmsales'),( indussales, 'indussales'))CS(col,val))K

    PIVOT(MAX(val)
    FOR TYPE IN
     ([ALL],[Cash],[Debt],[Equity]))P

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

You can do this by standard trick when first unpivoting data and then pivoting the result:

DECLARE @t TABLE(type NVARCHAR(MAX), fmsales MONEY, indussales MONEY)

INSERT INTO @t VALUES
('ALL', 0, 82.1),
('CASH', 0, 0),
('DEBT', 0, 62),
('EQUITY', 0, 20.1)


SELECT * FROM @t
UNPIVOT(a FOR products IN([fmsales],[indussales]))u
PIVOT (MAX(a) FOR type IN([ALL],[CASH],[DEBT],[EQUITY]))p

Output:

products    ALL     CASH    DEBT    EQUITY
fmsales     0.00    0.00    0.00    0.00
indussales  82.10   0.00    62.00   20.10

In order to apply this to your query you can use common table expressions(CTE) like:

;WITH cte AS(
             SELECT t.type ,
                    t.fmsales ,
                    t.indussales
             FROM   ( SELECT    CASE WHEN ( GROUPING(mti.type) = 1 ) THEN 'ALL'
                                     ELSE ISNULL(mti.Type, 'UNKNOWN')
                                END AS type ,
                                SUM(ISNULL(mti.SALES_THIS + mti.SWITCHIN_THIS, 0)) fmsales ,
                                SUM(ISNULL(mti.SALES_ALL + mti.SWITCHIN_ALL, 0)) indussales
                      FROM      dbo.IMonthly_trigger_Industry mti ,
                                dbo.RMMappingDb rm ,
                                dbo.EmployeeDB ed
                      WHERE     ed.EmployeeCode = 1217
                                AND rm.BranchName = mti.Branch
                                AND rm.ARNCode = mti.BROKER
                                AND rm.EmployeeCode = ed.EmployeeCode
                                AND mti.BROKER_NAME LIKE '%Pvt Limited%'
                      GROUP BY  mti.Type
                                WITH CUBE
                    ) t
             WHERE  t.type IS NOT NULL
             GROUP BY t.type ,
                    t.fmsales ,
                    t.indussales
        )
SELECT * FROM cte
UNPIVOT(a FOR products IN([fmsales],[indussales]))u
PIVOT (MAX(a) FOR type IN([ALL],[CASH],[DEBT],[EQUITY]))p

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use UNION ALL to first unpivot your data and then conditional aggregate to do the pivoting:

WITH Cte AS(
    --Your original query here
    SELECT 'ALL' AS type, 0 AS fmsales, 82.1 AS indussales UNION ALL
    SELECT 'CASH' AS type, 0 AS fmsales, 0 AS indussales UNION ALL
    SELECT 'DEBT' AS type, 0 AS fmsales, 62 AS indussales UNION ALL
    SELECT 'EQUITY' AS type, 0 AS fmsales, 20.1 AS indussales
),
CteUnpivot AS(
    SELECT 'fmsales' AS products, 'ALL' AS type, fmsales AS val FROM Cte WHERE type = 'ALL' UNION ALL
    SELECT 'fmsales' AS products, 'CASH' AS type, fmsales AS val FROM Cte WHERE type = 'CASH' UNION ALL
    SELECT 'fmsales' AS products, 'DEBT' AS type, fmsales AS val FROM Cte WHERE type = 'DEBT' UNION ALL
    SELECT 'fmsales' AS products, 'EQUITY' AS type, fmsales AS val FROM Cte WHERE type = 'EQUITY'UNION ALL
    SELECT 'indussales' AS products, 'ALL' AS type, indussales AS val FROM Cte WHERE type = 'ALL' UNION ALL
    SELECT 'indussales' AS products, 'CASH' AS type, indussales AS val FROM Cte WHERE type = 'CASH' UNION ALL
    SELECT 'indussales' AS products, 'DEBT' AS type, indussales AS val FROM Cte WHERE type = 'DEBT' UNION ALL
    SELECT 'indussales' AS products, 'EQUITY' AS type, indussales AS val FROM Cte WHERE type = 'EQUITY'
)
SELECT
    [ALL] = SUM(CASE WHEN type = 'ALL' THEN val ELSE 0 END),
    CASH = SUM(CASE WHEN type = 'CASH' THEN val ELSE 0 END),
    DEBT = SUM(CASE WHEN type = 'DEBT' THEN val ELSE 0 END),
    EQUITY = SUM(CASE WHEN type = 'EQUITY' THEN val ELSE 0 END)
FROM CteUnpivot
GROUP BY products

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17136

You can do this using a combination of PIVOT and UNPIVOT like below:

select * 
from
  (
      select * from t
  ) s
unpivot
  (
    val for product in ([fmsales],[indussales])
  )up
pivot
  (
    max(val) for type in ([ALL],[CASH],[DEBIT],[EQUITY])
   )p

Demo sql fiddle link:http://sqlfiddle.com/#!6/381f7/4

Explanation:

First we use UNPIVOT to get fmsales,indussales column-names as row data and then PIVOT over the type column. See intermediate results of UNPIVOT here:

http://sqlfiddle.com/#!6/381f7/5

Upvotes: 1

Related Questions