0

I am stuck with this problem, I'm doing select like this:

select * from mytable;

and getting following results

-------------
|NAME |VALUE|
-------------
|nam1 |val1 |
-------------
|nam2 |val2 |
-------------
|nam3 |val3 |
-------------

Result is always formatted like this, NAME->VALUE. Also, there are constraints placed on table so only one distinct NAME could appear in result. Also, values could be numbers, varchars, nulls, I don't want to do aggregation on this values.

Now I would like to convert this result to this:

--------------------
|NAM1 |NAM2 | NAM3 |
--------------------
|val1 |val2 | val3 |
--------------------

I tried achieving this result with pivot() function but without much success.

Thank you for your time, best regards :)

EDIT

This is the working example, with hardcoded column values, which is what I want to avoid.

select * from (select name, value from mytable)
pivot (min(value) for name in (
'nam1' as nam1
'nam2' as nam2
'nam3' as nam3
));
Ivan Pavlina
  • 45
  • 6
  • 1
    Show your query with `pivot`, please. "I don't want to do aggregation on this values." - `pivot` always require an aggregate function. But as soon as you have a name column with unique values, you can use `max` or `min` - they work with any data type and data don't look distorted. – Dmitriy Jan 12 '18 at 12:29
  • It's a simple pivot query from web tutorials, I'll edit the post. Also, i found this post https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/ which does exactly what I want, but requires implementation of custom code which unfortunately I'm not allowed to do in production enviroment. – Ivan Pavlina Jan 12 '18 at 13:18
  • 1
    Column values have to be harcoded, it is impossible to avoid, unfortunately. You can use a subquery if pivot result is converted to XML - [see example](https://stackoverflow.com/q/8759939/2141278). Result will be in an XML format, which you can parse further. Not a very straight way, sorry. – Dmitriy Jan 12 '18 at 13:41
  • Thank you for the response, I'll see if the XML way could be of any use. – Ivan Pavlina Jan 13 '18 at 01:31
  • If you don't want to hardcode column names you'll need to go down the route of dynamic code. There are several examples of this on StackOverflow. [Check them out](https://stackoverflow.com/search?q=oracle+dynamic+pivot) – APC Jan 13 '18 at 16:06

1 Answers1

0

Using DECODE Function:

select name,
decode(name,'Name1','value1',0) as Name1,
decode(name,'Name2','value2',0) as Name2,
decode(name,'Name3','value3',0) as Name3,
decode(name,'Name4','value4',0) as Name4
from mytable
group by name
order by name;
Rajeev Singh
  • 31
  • 5