Unpivot from existing query

Question

I have the following query:

select'Amount' as Amount,
('£'+ CAST(SUM(rc.[Fee Charge] +rc.[Fee Charge VAT] +rc.ExtraCharges+rc.ExtraChargesVAT+rc.OtherCharges+rc.OtherChargesVAT+rc.WaitingCharge+rc.[WaitingCharge VAT])AS nvarchar(50))) AS [CompletedTurnover],
('£'+ CAST(SUM(rin.[Fee Charge] +rin.[Fee Charge VAT] +rin.ExtraCharges+rin.ExtraChargesVAT+rin.OtherCharges+rin.OtherChargesVAT+rin.WaitingCharge+rin.[WaitingCharge VAT])AS nvarchar(50))) AS [In Progress Turnover],
('£'+ CAST(SUM(run.[Fee Charge] +run.[Fee Charge VAT] +run.ExtraCharges+rc.ExtraChargesVAT+run.OtherCharges+run.OtherChargesVAT+run.WaitingCharge+run.[WaitingCharge VAT])AS nvarchar(50))) AS [Unallocated Turnover],
123 as [Credit Note Value]


from tblreservation R 
left join tblreservation rc on R.ReservationsID = rc.reservationsid and rc.Completed = 1
left join tblreservation rin on R.reservationsid = rin.reservationsid and rin.InProgress = 1
left join tblreservation run on Run.ReservationsID = r.ReservationsID and run.completed = 0 and run.inprogress = 0

This returns data like so:

CompletedTurnover      In progress Turnover      Unallocated Turnover       Credit Note Value    
1202039920             23998858945               9384585845                 123

This is as expected. However, I need the following output and I'm struggling a bit using pivots.

  Completed Turnover    1202039920             
  In Progress Turnover  23998858945               
  Unallocated Turnover  9384585845                 
  Credit Note Value     123

Any help would be greatly appreciated.

Answer 1

You can use a union to get the results you need:

select 'Completed turnover' Description, 
        (  '£'+ CAST(SUM(rc.[Fee Charge] +
                     rc.[Fee Charge VAT] + 
                     rc.ExtraCharges+
                     rc.ExtraChargesVAT+
                     rc.OtherCharges+
                     rc.OtherChargesVAT+
                     rc.WaitingCharge+
                     rc.[WaitingCharge VAT]
                   )AS nvarchar(50))) value
from ....

union all

select 'In Progress turnover', .....
from ....

union all

select 'Unallocated Turnover', .....
from ....

you probably want to look at using in conjunction with a CTE