Transpose using Dynamic SQL

Database
Enthusiast

Transpose using Dynamic SQL

Matrix is to be created using aggregate values of different Expenseid's for the employees.

Matrix table

Empl Exp1 Exp2 Exp3 Exp4
1 1000 2000 1200 2300
2 3212 1800 900 1850

Source table looks like

Empid ExpID Amt
1 1 300
1 2 400
1 1 100
1 2 200
1 1 300
1 2 800
1 1 300
1 2 600

Intermediate table has unique ExpID.

Can anyone help me in getting the output as desired using dynamic sql or anyother method?

3 REPLIES
Enthusiast

Re: Transpose using Dynamic SQL

Two ways to answer your problem :

Enthusiast

Re: Transpose using Dynamic SQL

Two ways to answer your problem :

select empid
, sum(exp1) as exp1
, sum(exp2) as exp2
, sum(exp3) as exp3
, sum(exp4) as exp4
from
( select empid
, sum(amt) as exp1
, 0 as exp2
, 0 as exp3
, 0 as exp4
from source
where expid = 1
group by empid
union
select empid
, 0 as exp1
, sum(amt) as exp2
, 0 as exp3
, 0 as exp4
from source
where expid = 2
group by empid
...
) as tmp
group by tmp
;

select empid
, sum(case expid when 1 then amt end) as exp1
, sum(case expid when 2 then amt end) as exp2
, sum(case expid when 3 then amt end) as exp3
, sum(case expid when 4 then amt end) as exp4
from source
group by empid
;
Enthusiast

Re: Transpose using Dynamic SQL

That is a valid answer.

What if my expense id keeps changing, but I have a reference table which holds all the expense and I need to aggregate dynamically?