CODEWITHSUNDEEP
oracle-databaseoracle11goracle10goracle-sqldeveloperoracle-apex
user757321
user757321

Reputation: 341

How to convert matrix data into columns

hi i have thousands records in a signle column of my oracle database table like this

enter image description here

I want to display data in 4 columns with the help of SQL query here is my desire results enter image description here

so in this example, I have a total 24 records and I want to display 6 records each column

Upvotes: 0

Views: 99

Answers (1)

MT0
MT0

Reputation: 167867

Use a PIVOT:

SELECT A, B, C, D
FROM   (
  SELECT value,
         MOD( ROWNUM - 1, 4 ) AS col,
         CEIL( ROWNUM / 4 ) AS rn
  FROM   table_name
)
PIVOT( MAX( value ) FOR col IN (
  0 AS A,
  1 AS B,
  2 AS C,
  3 AS D
) )
ORDER BY rn;

So if your data is:

CREATE TABLE table_name ( value ) AS
SELECT ROUND(DBMS_RANDOM.VALUE(1,1000))
FROM   DUAL
CONNECT BY LEVEL <= 25;

Then it outputs:

  A |    B |    C |    D
--: | ---: | ---: | ---:
754 |  622 |  265 |  181
566 |  801 |  844 |  320
938 |  377 |  919 |  306
921 |  402 |  737 |  143
562 |   94 |  868 |  790
617 |  809 |  437 |  469
264 | null | null | null

db<>fiddle here

Upvotes: 1

Related Questions