1

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

4
  • 3
    Did you try anything? Read the documentation for pivot? Google? Commented Jan 11, 2017 at 13:00
  • Yes. Trying but for now, I don't have solution... Commented Jan 11, 2017 at 13:23
  • 2
    okay, great, post what you've tried so far. Commented Jan 11, 2017 at 13:24
  • First, get simple table with query: if OBJECT_ID ('tempdb..#tps_pivot') is not null drop table #tps_pivot select [total cnt] = SUM(tps.first_count) + SUM(tps.second_count) ,[total amnt] =SUM(tps.first_amount) + SUM(tps.second_amount) into #tps_pivot from tps Commented Jan 11, 2017 at 13:32

1 Answer 1

2

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....

1
  • No, this works like charm. Thanks a lot !!! Commented Jan 11, 2017 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.