Divide rows into specific columns in SQL (Oracle)

user_4334781 0
Viewed 65

Been trying a scenario where i want to spread out the return data into several columns instead of lengthy rows

e.g.

COL1       |  COL2    |   COL3
-----------------------------
DATAHEAD   |  VALUE1  | VALUE9
DATAHEAD   |  VALUE2  | VALUE10 
DATAHEAD   |  VALUE3  | VALUE11
DATAHEAD   |  VALUE4  | VALUE12
DATAHEAD   |  VALUE5  | VALUE13
DATAHEAD   |  VALUE6  | VALUE14
DATAHEAD   |  VALUE7  | VALUE15
DATAHEAD   |  VALUE8  | VALUE16 

I want it to spread  into 6/n columns dynamically like 
this below

COL1       |  COL2     |   COL3   | COL4     | COL 5  |    COL6  |
--------------------------------------------------------------------
DATAHEAD   |  VALUE1   | VALUE2   |  VALUE3  | VALUE4  |  VALUE5  |
DATAHEAD   |  VALUE6   | VALUE7   | VALUE8   |  VALUE9 |  VALUE10 |
DATAHEAD   |  VALUE11  | VALUE12  |  VALUE13 | VALUE14 |  VALUE15 |
DATAHEAD   |  VALUE16  |  Null    | Null     |  Null   |  Null    |

Doesn't matter with the number of data as long as it spreads out like the above format. I saw some related articles about Pivot() but I cannot find out how to apply it in this scenario. Your help would be graetely appreciated as I'm not that good in sql.

pivot oracle sql
1 Answers

First, I made a sorted base of cells into a pivot table.

    select col1, col2 as col from table1
     union
    select col1, col3 as col from table1 
;

Then I provided information in which row d and column m would be the values.

  select
    col1,
    col,
    trunc((rank() over (order by col)-1)/5) as d,
    mod(rank() over (order by col)-1,5) as m
  from
  (
    select col1, col2 as col from table1
     union
    select col1, col3 as col from table1 
  )
;

Finally, I did a pivot according to the documentation.



select * from
(
  select
    col1,
    col,
    trunc((rank() over (order by col)-1)/5) as d,
    mod(rank() over (order by col)-1,5) as m
  from
  (
    select col1, col2 as col from table1
     union
    select col1, col3 as col from table1 
  )
)
pivot
(
  min(col) for m in (0 as COL2,1 as COL3,2 as COL4,3 as COL5,4 as COL6)
)
order by d
;

Ouput:

COL1 D COL2 COL3 COL4 COL5 COL6
DATAHEAD 0 VALUE01 VALUE02 VALUE03 VALUE04 VALUE05
DATAHEAD 1 VALUE06 VALUE07 VALUE08 VALUE09 VALUE10
DATAHEAD 2 VALUE11 VALUE12 VALUE13 VALUE14 VALUE15
DATAHEAD 3 VALUE16 (null) (null) (null) (null)

DDL:

CREATE TABLE Table1
    ("COL1" varchar2(8), "COL2" varchar2(7), "COL3" varchar2(7))
;

INSERT ALL 
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE01', 'VALUE09')
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE02', 'VALUE10')
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE03', 'VALUE11')
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE04', 'VALUE12')
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE05', 'VALUE13')
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE06', 'VALUE14')
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE07', 'VALUE15')
    INTO Table1 ("COL1", "COL2", "COL3")
         VALUES ('DATAHEAD', 'VALUE08', 'VALUE16')
SELECT * FROM dual
;

Edit 1:

The rank over function in Oracle is used to number the rows in order.

For example, rank() over (order by col) is a column in which there are numbers from 1, and if you arrange the column by col, it will be 1, 2, 3, ... If you rearrange the rows, the rank will still retain the value according to the col column.

So my previous solution arranges the values alphabetically regardless of their location in the source table.

If you would like to sort by the first column and sort the second in the same order at the end, you can use this inner select:

    select
      col1,
      rank() over (order by col2) as r,
      col2 as col
    from table1
    union
    select 
      col1,
      rank() over (order by col2) + (select count(*) from table1) as r,
      col3 as col
    from table1 
;

And then the resulting select will look like this:

select * from
(
  select
    col1,
    col,
    trunc((r-1)/5) as d,
    mod(r-1,5) as m
  from
  (
    select
      col1,
      rank() over (order by col2) as r,
      col2 as col
    from table1
    union
    select 
      col1,
      rank() over (order by col2) + (select count(*) from table1) as r,
      col3 as col
    from table1 
  )
)
pivot
(
  min(col) for m in (0 as COL2,1 as COL3,2 as COL4,3 as COL5,4 as COL6)
)
order by d
;
user_115822700
Related