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

  • 3
    Did you try anything? Read the documentation for pivot? Google? – Erik Darling Jan 11 '17 at 13:00
  • Yes. Trying but for now, I don't have solution... – Darko Milic Jan 11 '17 at 13:23
  • 2
    okay, great, post what you've tried so far. – Erik Darling Jan 11 '17 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 – Darko Milic Jan 11 '17 at 13:32
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....

  • No, this works like charm. Thanks a lot !!! – Darko Milic Jan 11 '17 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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