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

Question

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:

What I’m trying to achieve is:

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

You can't. What you can is to add the Stutus in the Amount column with concatenation. — gotqn
– gotqn, Commented Oct 23, 2017 at 15:26
This is a presentation layer thing, not a sql server thing. In sql server we return columns and your headers contradict the concept of columns. You really should post data as something consumable instead of pictures. I was going to show you how you can do everything other than those headers but I just don't have to patience to type in your sample data. — Sean Lange
– Sean Lange, Commented Oct 23, 2017 at 15:33
@Sean Lange Sorry, I've added sample query, maybe this will help? — Iva Bazhunaishvili
– Iva Bazhunaishvili, Commented Oct 23, 2017 at 16:57
Not really. The reason we want data is so we can write a query and test it. — Sean Lange
– Sean Lange, Commented Oct 23, 2017 at 16:58

Sean Lange · Accepted Answer · 2017-10-23 18:25:47Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related