How to use PIVOT here?

Question

I have this table

+--------+--------------+----------------+--------------+----------------+
|  XPK   | First_count  |  First_amount  | Second_count |  Second_amount |
+--------+--------------+----------------+--------------+----------------+
|      1 |     10       |       2000     |     20       |       5000     | 
+--------+--------------+----------------+--------------+----------------+

Need to get result like this, sum and pivot:

+-------------+------------+
|Total_count  |     30     |
|Total_amount |    7000    |
+-------------+------------+

I need to use pivot, but how?

Using MS SQL Server 2014

Answer 1

To be honest, for such need, I would not use pivot, I would simply:

select XPK, 'Total_count', First_count + Second_count
from my_table
union
select XPK, 'Total_amount', First_amount + Second_amount
from my_table

If you really want to use pivot you can try:

select XPK, kpi, total
from (
select XPK,  First_count + Second_count as Total_count, First_amount + Second_amount as Total_amount
from my_table
) t
unpivot (total for kpi in(Total_count, Total_amount)) tt

But maybe your problem is more complex than what you described....