Pivot column into row

848002

Hello All,

Having an hard time trying to make a pivot in SQL.

E.G. :

label	value_text	value_date	id
Po #	345	NULL	112
Po #	123456	NULL	120
Po #	935131	NULL	134

So I would like to have the label section as a column name and put the value_text under the appropriate column but want to keep the ID in order to make a table with my view and join it to another table on a sql request.

Is that possible ?

Regards,

Eric

L-MachineGun

848002 wrote:

Hello All,

. . .   E t c   . . .


Is that possible ?

Regards,

Eric

Yes, but you would only have one column "Po #"

Frank Kulash

Hi, Eric,

Sorry, it's not clear what you want.

Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
Always say which version of Oracle you're using (for example, 11.2.0.2.0).
See the forum FAQ: https://forums.oracle.com/message/9362002#9362002

The compiler needs to know any column names or aliases that are used in a query.  Only after the statement is compiled does it start to fetch data.  Therefore, you can't have column names or aliases that depend on the data fetched.

If you want to have dynamic column names, then you need dynamic SQL.  Your front end may have tools that make dynamic SQL easier.  In SQL*Plus, for example, you can use substitutiuon variables.

848002

Thanks,

As I've already transform my view into a table , here are my current statement :

SELECT * FROM table.df;

Then here is my result :

label	value_text	value_date	id
P/O line#	345	NULL	1
P/O line#	50	NULL	2
P/O line#	10	NULL	3
P/O line#	10	NULL	1
P/O line#	10	NULL	2
P/O line#	10	NULL	3
P/O line#	30	NULL	1
P/O line#	10	NULL	2
P/O line#	20	NULL	3
P/O line#	10	NULL	1
P/O line#	10	NULL	3
Po #	345	NULL	3
Po #	123456	NULL	2
Po #	935131	NULL	4
Po #	931703	NULL	5
Po #	12345	NULL	6
Po #	935773	NULL	7

And this is what I would like to have :

Po #	P/O line#	value_date	id
345	345	NULL	1
123456	50	NULL	2
935131	10	NULL	3
931703	10	NULL	1
12345	10	NULL	2
935773	10	NULL	3
505414	30	NULL	1
504884	10	NULL	2
504884	20	NULL	3
934322	10	NULL	1

Hope that is more clear,

Eric

Frank Kulash

Hi, Eric,

That sure does look like a pivot.  "MIN (CASE ...) ... GROUP BY" might be simpler than "SELECT ... PIVOT" in this case.  A self-join is another possibility.

I can't figure out what each row of the output represents.  For example, why do you want

Po #    P/O line#  value_date  id
------  ---------  ----------  --
123456  50         NULL        2
 

in the result set, and not

Po #    P/O line#  value_date  id
------  ---------  ----------  --
123456  10         NULL        2
 

or would you be equally satisfied with either one?  Once again, post CREATE TABLE and INSERT statements to reproduce the data (no matter where the data actually comes from) and explain how you get the desired results from the given data.

Remember, there is no built-in order to rows in a relational database, so if you use words like "first" or "next" in your explanation, be sure to say what it is in the data that makes a row first or next.