I have a list of data and that data repeats a lot, plainly down to the fees. There are two types of fees; TypeA and TypeB.

I currently have:

Person	Value	Type
John	10	TypeA
John	10	TypeA
John	20	TypeB
John	20	TypeB
Steve	15	TypeA
Steve	15	TypeA
Steve	25	TypeB
Steve	25	TypeB

What I want is:

Person	TypeA	TypeB
John	20	40
Steve	30	50

Edit: I'm using SQL Server

CodePudding user response：

SELECT T.PERSON,
SUM(
    CASE
       WHEN T.TYPE='TypeA' THEN T.Value
    ELSE 0
   END
   )AS SUM_TYPE_A,
SUM(
    CASE
      WHEN T.TYPE='TypeB' THEN T.Value
      ELSE 0
    END
   )AS SUM_TYPE_B
FROM YOUR_TABLE AS T
GROUP BY T.PERSON

CodePudding user response：

If there's literally only two types, you can easily do this with conditional aggregation:

select
person,
sum (case when type = 'TYPEA' then value end) as typeA,
sum (case when type = 'TypeB' then value end) as typeB
from
<table>
group by
person

CodePudding user response：

In SQL Server you can also write dynamic pivot. Specially it's great when you don't know all the name of the pivoted columns.

Schema and insert statements:

create table tablename(Person varchar(50), Value int, Type varchar(50));
insert into tablename values('John',10,'TypeA');
insert into tablename values('John',10,'TypeA');
insert into tablename values('John',20,'TypeB');
insert into tablename values('John',20,'TypeB');
insert into tablename values('Steve',15,'TypeA');
insert into tablename values('Steve',15,'TypeA');
insert into tablename values('Steve',25,'TypeB');
insert into tablename values('Steve',25,'TypeB');

Query:

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

select @cols=string_agg(type,',') within group (order by type)
from
(select distinct type from tablename)t

set @query = 
'SELECT Person, '   @cols   ' from 
(
    select * from tablename    
) src
pivot 
(
    sum(value) for Type in ('   @cols   ')
) piv
order by Person'

execute(@query)

Output:

Person	TypeA	TypeB
John	20	40
Steve	30	50

fiddle