Using Column Alias as Values in other columns of table in Case statement

I have a sql table that has columns FundId, BreakdownName, BreakdownType and BreakdownValue

FundId     BreakdownName   BreakdownType BreakdownValue
F00000OCVS  Cash               Long         10.69
F00000OCVS  Cash               Net          3.17

I want result table show fundid and Breakdownvalue as alias of combination of breadkdownname and breakdowntype

so for example, above table should result into

FundId      CashLong   CashNet
F00000OCVS  10.69       3.17

I tried doing it with case statement as

select
a.FundID,
b.CashLong,
b.CashNet
from  a
 inner join ( select FundID,
    case when BreakdownName = 'Cash' and BreakdownType = 'Long' then BreakdownValue end as 'CashLong',
    case when BreakdownName = 'Cash' and BreakdownType = 'Net' then BreakdownValue end as 'CashNet'
from Morningstar.dbo.mstardatawarehouseassetallocationbreakdownvertical
where FundShareClassID = 'F00000OCVS') b
on a.FundShareClassID = b.FundShareClassID
where a.FundShareClassID = 'F00000OCVS'

But this is giving me multiple rows for fund. First row has value for CashLong but CashNet is null. Second row has value for CashNet but CashLong is null. I want to have them in single row.

I tried doing it using temp table and insert but same result.

Can someone please help me with it?

Answer

Pretty sure you can do easily with conditional aggregation. Something like this.

select bdv.FundId
    , CashLong = max(case when bdv.BreakdownType = 'Long' then bdv.BreakdownValue end)
    , CashNet = max(case when bdv.BreakdownType = 'Net' then bdv.BreakdownValue end)
from Morningstar.dbo.mstardatawarehouseassetallocationbreakdownvertical bdv
group by bdv.FundId

Advertisement

Answer