Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Convert rows into columns

FirstName_LastNameMar 8 2016 — edited Mar 8 2016

Hi,

I have situation when I am trying to convert rows into columns. Here is the scenario -

1. I have a function which takes in a SQL query as input and returns the result as a table type.

2. The result is in the following fashion -

     a. Say I pass "Select * from emp" as the input

     b. Result would be

     

Column NameColumn Value
Emp No1001
Emp NameJack
Dept No20
CityNew York
Emp No1002
Emp NameMark
Dept No30
CityChicago

3. What I am trying to achieve is that I need to convert the rows into columns. Something like below -

     Emp No          Emp Name          DeptNo              City

     1001               Jack                    20               New york

     1002               Mark                    30               Chicago

4. I tried using the pivot function, but that requires to pass aggregate function and provide values for those aggregate - which is not required in my case.

5. I also  cannot hard-code the values as the example above is only for emp table. I can pass any query as input and it should give the result.

Any idea how this can be achieved ?

Thanks!

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 5 2016
Added on Mar 8 2016
8 comments
847 views