0

I have the following table format:

 ID | Key | Value
 --   ---   -----
 1     A     aa    
 2     B     bb
 3     A     ay
 4     C     cc
 5     B     bx
 6     C     ct

I need to convert this table to following format:

Output:

 A    B    C
---  ---  ---
aa   bb   cc
ay   bx   ct

I looked for PIVOT function in oracle 11g, but the "Key" values in input table is not a fixed set of values, they can be anything. I also looked for other such questions but I am not sure in my case, how the query should be written.

Any help will be appreciated, thanks!

Edited:

For the solution, I want to execute the following query but it gives me error at subquery of IN clause. I don't understand why is that.

Select * from (Select Key, Value, Id from tableName
pivot (max(Value) for Key IN (SELECT distinct Key from tableName)));

Thanks!

OutOfMind
  • 874
  • 16
  • 32

2 Answers2

1

You can apply the pivot function as below. 

   select * from
    (select Key,Value from yourtable) 
    pivot(max(Value) for Key in ('A', 'B', 'C'));

A subquery in pivot is used only in conjunction with the XML keyword

Make your query as below:

Select * from (Select Key, Value, Id from tableName)
pivot xml (max(Value) for Key IN (SELECT distinct Key from tableName));
Tom J Muthirenthi
  • 3,028
  • 7
  • 40
  • 60
  • Thanks @Tom but making the parentheses as you suggested, did not solve the problem. I dont know why I find no such example for Pivot with sub query. – OutOfMind Mar 16 '17 at 11:34
  • 1
    I think you have to use `pivot xml` for using sub-query. You will get the output as in xml format. – Tom J Muthirenthi Mar 16 '17 at 12:10
  • Hmm, but I need the output in tabular form without xml tags, so I would rather first execute the sub query and use its result in the Pivot query. Thanks! – OutOfMind Mar 16 '17 at 12:17
  • @OutOfMind I have seen such questions for converting xml in to tabluar forms in SO. But ithink they uses cursor or procs or something like that. Better you can open a new question for that. Even I am curious to know the answer :) – Tom J Muthirenthi Mar 16 '17 at 12:29
  • Hey @Tom, posted a question for it: http://stackoverflow.com/questions/42851887/convert-pivot-xml-output-to-tabular-output-without-xml – OutOfMind Mar 17 '17 at 07:43
1

You can use dynamic sql as you have said that the key is not fixed create a string for keys this will help you. pass this string to you pivot function as the keys are in string so this will help you.

Rohit Gupta
  • 455
  • 4
  • 16
  • Thanks @Rohit, I edited the question, please take a look at it. Does Pivot function allow such a subquery? Or I would have to use Dynamic Sql as per your suggestion? – OutOfMind Mar 16 '17 at 07:05
  • 1
    I think sub query wont help you please go the link for understanding how dynamic query in pivot table will help you. http://stackoverflow.com/questions/13245364/ms-sql-server-pivot-table-with-subquery-in-column-clause – Rohit Gupta Mar 16 '17 at 07:38
  • @RohitGupta The link you have added is foe Sql Server. The question is for `oracle` – Tom J Muthirenthi Mar 16 '17 at 08:49
  • Sir the question has also the tag for sql server and the link will help how this can be implemented in oracle as well. – Rohit Gupta Mar 16 '17 at 08:58