if I have data in one column in Oracle table

A1
A2
C1
D2
B1
C2
B3
D4
A3, and so on

How to make skript to sort data in more columns like this

A1 B1 C1 D2
A2 B3 C2 D4
A3

CodePudding user response：

Here's one option.

Sample data:

SQL> select * from test;

CO
--
A1
A2
C1
D2
B1
C2
B3
D4
A3

9 rows selected.

Query:

• temp cte returns row numbers for each row in a source table
• temp2 calculates "groups" (so that 4 columns make one row), while rn2 represents position of each column in that row (from position 1 to position 4)
• final query just returns one column value per group in each "newly created" column (up to 4 of them).

SQL> with temp as
  2    (select col,
  3            row_number() over (order by rowid) rn
  4     from test
  5    ),
  6  temp2 as
  7    (select col,
  8            ceil(rn / 4) grp,
  9            case when mod(rn - 4, 4) = 0 then 4
 10                 when mod(rn - 4, 3) = 0 then 3
 11                 when mod(rn - 4, 2) = 0 then 2
 12                 else 1
 13            end rn2
 14     from temp
 15    )
 16  select min(case when rn2 = 1 then col end) c1,
 17         min(case when rn2 = 2 then col end) c2,
 18         min(case when rn2 = 3 then col end) c3,
 19         min(case when rn2 = 4 then col end) c4
 20  from temp2
 21  group by grp;

C1 C2 C3 C4
-- -- -- --
C1 A2 A1 D2
B1 C2 B3 D4
A3

SQL>

CodePudding user response：

I don't think it's possible to "squash" the aggregated results exactly the way how you're showing. Keep in mind that you're working with structured data and relations.

In your example, you show the first tuple:

A1 B1 C1 D2

however, in the real table, neither A1 B1 C1 D2 are related to each other (horizontally).

You could use multiple CASE statements within a SELECT clause to determine in which "result-set" column to display the data. This will give you a result similar to:

A.   B.   C.   D.   
-------------------
A1.  null null null
A2.  null null null
null null C1.  null
...

It will be up to your program to later read the result and remove nulls.

select 
    case value when 0 then 1 else null end as A,
    case value when 1 then 1 else null end as B,
    case value when 2 then 1 else null end as C,
    case value when 3 then 1 else null end as D,
    case value when 4 then 1 else null end as E
from events

The "drawback" I see is that you have to define the "hard-coded" columns, which may or may not be acceptable (according to your use case)

SQL fiddle: http://sqlfiddle.com/#!9/63dd887

CodePudding user response：

This is best done in a middle-tier application (Java, C#, PHP, etc.) but if you want to do it in SQL, you can PIVOT:

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

Which, for the sample (random) data:

CREATE TABLE table_name (value) AS
SELECT CHR(64 FLOOR(DBMS_RANDOM.VALUE(1,5))) || FLOOR(DBMS_RANDOM.VALUE(1,5))
FROM   DUAL
CONNECT BY LEVEL <= 42;

May output:

A	B	C	D
B2	D1	C4	D3
C3	B3	C2	A1
A3	B2	B4	D1
A2	B4	D1	B1
C3	D2	C4	D4
C4	D1	D4	B3
C2	C3	A4	C1
D3	A4	B4	A3
A4	C3	C1	A2
B2	C4	A2	B3
D1	C2

db<>fiddle here