How to convert matrix data into columns

Question

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

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

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

Why would you want to do that? Is there a condition defining which number goes to which column? — Pavel Smirnov, Apr 26 '20 at 13:26
Does this answer your question? [Distribute arbitrary rows on multiple columns](https://stackoverflow.com/questions/34973457/distribute-arbitrary-rows-on-multiple-columns) — Makdous, Apr 26 '20 at 13:31
@PavelSmirnov no condition just want to equal data goes in each column — user757321, Apr 26 '20 at 13:44

score 1 · Accepted Answer · answered Apr 26 '20 at 15:57

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

How to convert matrix data into columns

1 Answers1