How can convert a list to matrix in oracle?

Question

How can I convert a list contain 3 column ("x", "y", "value") to a matrix with n rows and n columns?(n equals to count(distinct x))

something like this:

|x    | y | z|
--------------
|a1   | a2| 3|
|a1   | a3| 5|
|a2   | a3| 9|
|a3   | a3| 0|
|.    | . | .|
|.    | . | .|
|a5000| a3| 1|

to the below :

|x     |a1     |a2    |a3 . .   |a5000
---------------------------------------
|a1    |null   |3     |5   ..   |null
|a2    |null   |null  |9   ..   |null
|a3    |null   |null  |0   ..   |null
|.     | .     | .    |    ..   |.
|.     | .     | .    |    ..   |.
|a5000 |null   |null  |1   ..   |null

I can't use pivot because I can't write all of y value in query.

Mmm... It seems like this: http://stackoverflow.com/questions/10929108/rotate-pivot-table-with-aggregation-in-oracle — UltraCommit, Jul 07 '15 at 10:53
I can't use something like that, because I have too many attribute(columns). — parvij, Jul 07 '15 at 10:57

score 0 · Accepted Answer · answered Jul 07 '15 at 11:29

I assume you want something like:

SELECT * 
  FROM (SELECT * FROM YOUR_TABLE) 
  PIVOT (MAX(Z) 
         FOR Y IN ('A1','A2','A3'..., 'A5000')) 
  ORDER BY TO_NUMBER(SUBSTR(X,2));

And as you mention, you cannot write the query because there are too many 'A' values.

But you can write a query that writes your query:

SELECT 'SELECT * FROM (SELECT * FROM YOUR_TABLE) PIVOT (MAX(Z) FOR Y IN ('
    || LISTAGG('''A'||(LEVEL)||'''', ',') WITHIN GROUP (ORDER BY LEVEL) 
    || ')) ORDER BY TO_NUMBER(SUBSTR(X,2))'
  FROM DUAL 
  CONNECT BY LEVEL <= (SELECT COUNT(DISTINCT X) FROM YOUR_TABLE);

This query will build the query you want. Then launch the resulting query as any other dynamic select.

You're right, but I find that oracle can't have more that 1000 columns. — parvij, Jul 11 '15 at 07:34

How can convert a list to matrix in oracle?

1 Answers1