Convert rows to columns using PIVOT

Question

I have a table named AmountList in SQL Server :

where Primary key is " UniqueCol1, UniqueCol2, AmountID "

and on the basis of AmountID I want to Pivot the results as:

I am facing the challenge using Pivot because only Aggregate functions can be used but I need actual values of the fields.

UPDATE: Added my attempt for the solution using JOINS, but need some query with higher performance.

Answer 1

You can use case expressions to achieve your expected output.

select
  uniqueCol1,
  uniqueCol2,
  sum(case when AmountID = 1 then amount end) as Amount1MO,
  sum(case when AmountID = 3 then amount end) as Amount3MO,
  sum(case when AmountID = 6 then amount end) as Amount6MO,
  sum(case when AmountID = 9 then amount end) as Amount9MO,
  sum(case when AmountID = 12 then amount end) as Amount12MO
from AmountList
group by
  uniqueCol1,
  uniqueCol2