Pivot help

donovan7800

Say so I have simple data like this:

CREATE TABLE assembly

(

assy_no VARCHAR(30),

assy_bld VARCHAR(30)

);

INSERT INTO assembly SELECT '12345H','235ASD33' FROM dual;

INSERT INTO assembly SELECT '5AS352','YV457SS2' FROM dual;

INSERT INTO assembly SELECT 'GFDG42','887AVNSA' FROM dual;

SELECT * FROM assembly;

ASSY_NO    ASSY_BLD

---------- ----------

12345H     235ASD33  

5AS352     YV457SS2  

GFDG42     887AVNSA  

          

I want it to look like this:

COL_1       COL_2      COL_3      COL_4

--------    --------   --------   --------

ASSY_NO     12345H     5AS352     GFDG42    

ASSY_BLD    235ASD33   YV457SS2   887AVNSA  

I don't really care what the names of the column headers are, I just want to have each table column on it's own row with the their values showing horizontally across.

I'm not that sure how to do that with PIVOT but I assume it's possible?

Frank Kulash

Hi,

The number of columns in the result set has to be hard-coded into the query.  If you want the number of columns to depend on how many rows are in the table, then you need dynamic SQL.  See the forum FAQ for some ideas.

https://forums.oracle.com/message/9362005#9362005

You may be interested in these threads:

https://community.oracle.com/thread/3537540

https://forums.oracle.com/message/10965262

https://forums.oracle.com/message/3529823