CODEWITHSUNDEEP
sql-servert-sqlpivot
Iva Bazhunaishvili
Iva Bazhunaishvili

Reputation: 61

T-SQL applying pivot for two rows under a single column

On SQL Server 2014 I use the following code:

CREATE TABLE TempTable
(
LType varchar(255),
LStatus varchar(255),
PAmount decimal(16,2),
RAmount decimal(16,2));

INSERT INTO TempTable (LType, LStatus, PAmount, RAmount)

VALUES ('TypeA','StatusA', '1000', '10'),
       ('TypeB', 'StatusC', '500', '50'),
       ('TypeC', 'StatusB', '2500', '100'),
       ('TypeB', 'StatusB', '1000', '50'),
       ('TypeA', 'StatusA', '3000', '25'),
       ('TypeC', 'StatusB', '2200', '50');



       Select Ltype, Lstatus, SUM(PAmount) as PAmount, SUM(RAmount) as RAmount

        From TempTable 

        Where PAmount > 0 
        Group By LType, LStatus

to get this table: enter image description here

What I’m trying to achieve is: enter image description here

I used pivot but was unable to apply it simultaneously for PAmount and RAmount under Status columns. Can anyone help with solution?

Upvotes: 0

Views: 176

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

You can use conditional aggregation for this. This assumes you will always have these values. If you need this to be dynamic then there is a bit more work to do.

select StatusA_PAMount = max(case when Totals.Lstatus = 'StatusA' then Totals.PAmount end)
    , StatusA_RAMount = max(case when Totals.Lstatus = 'StatusA' then Totals.RAmount end)
    , StatusB_PAMount = max(case when Totals.Lstatus = 'StatusB' then Totals.PAmount end)
    , StatusB_RAMount = max(case when Totals.Lstatus = 'StatusB' then Totals.RAmount end)
    , StatusC_PAMount = max(case when Totals.Lstatus = 'StatusC' then Totals.PAmount end)
    , StatusC_RAMount = max(case when Totals.Lstatus = 'StatusC' then Totals.RAmount end)
from 
(
    Select Lstatus
        , SUM(PAmount) as PAmount
        , SUM(RAmount) as RAmount
    From TempTable 
    Where PAmount > 0 
    Group By LStatus
) Totals

Upvotes: 2

Related Questions