0

My data as follow:

> mydata

   month count1 count2 count3
1 201301    100    110     50
2 201302    150    160     60
3 201303    200    210     50

My expected data like this:

>final data

count_all  201301 201302 201303
1 count1       100    150    200
2 count2       110    160    210
3 count3        50     60     50

How can I get the result using SQL? I know PIVOT a little, but it seems there are some problems in the column name during my processing. I need an efficient way to resolve this problem. And I will execute such a query:

select * from final_data where count_all='count1'

So if not with this row-column transformation, I don't know how to get a similar result.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
jasmine_007
  • 87
  • 1
  • 3
  • 12
  • have you tried any query? then please share – Maz I Feb 18 '14 at 06:05
  • 2
    http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server-2008 http://stackoverflow.com/questions/20119162/transpose-rows-into-columns-in-sql-server-2008-r2 – Revan Feb 18 '14 at 06:09
  • Which DBMS are you using? This is critical because solutions that work in one will not necessarily work in others. – Jonathan Leffler Feb 18 '14 at 06:47
  • `@Jonathan Leffler` HANA,SAP's DATABASE.And this is one of the problems, but not a major concern. – jasmine_007 Feb 18 '14 at 07:04
  • You should Unpivot and Pivot together, which is very complex and you may not get what you want because of aggregate functions. Does it have to be this way? What do you want to query in the end? – Kuzgun Feb 18 '14 at 07:10
  • `@ Kuzgun`Yes,because I need to get the record like this:`select * from final_data where count_all=count1`. That means I need to know the number of `count1` from `201301` to `201303`.If without the `column-row` transformation,I don't know how to realize this by another way.So do you have any ideas without transformation? – jasmine_007 Feb 18 '14 at 07:22

0 Answers0