CODEWITHSUNDEEP
oracle-database
WildFly
WildFly

Reputation: 55

Oracle sort values into column

I have a table like this:

time        length  name 
00:01:00         2    a 
00:11:22         2    a 
01:01:00        45    a 
00:23:00         3    b

and I want to retrieve data from the table in the form:

a                        b
time        length       time length
00:01:00         2   00:23:00      3
00:11:22         2    
01:01:00         2

so it is a simple task of rearranging data, atm I am doing this in a bash script, but I wonder if there is an easy way to do it in Oracle?

Upvotes: 1

Views: 46

Answers (2)

Alex Poole
Alex Poole

Reputation: 191580

You can use a multi-column pivot, by adding an extra column that links the related A and B values; presumably by time order, something like:

select time_col, length_col, name_col,
  dense_rank() over (partition by name_col order by time_col) as rnk
from your_table;

TIME_COL LENGTH_COL N        RNK
-------- ---------- - ----------
00:01:00          2 a          1
00:11:22          2 a          2
01:01:00         45 a          3
00:23:00          3 b          1

and then pivot based on that:

select *
from (
  select time_col, length_col, name_col,
    dense_rank() over (partition by name_col order by time_col) as rnk
  from your_table
)
pivot (
  max(time_col) as time_col, max(length_col) as length_col
  for name_col in ('a' as a, 'b' as b)
);

       RNK A_TIME_C A_LENGTH_COL B_TIME_C B_LENGTH_COL
---------- -------- ------------ -------- ------------
         1 00:01:00            2 00:23:00            3
         2 00:11:22            2                      
         3 01:01:00           45

I've left the rnk value in the output; if you don't want that you can list the columns in the select list:

select a_time_col, a_length_col, b_time_col, b_length_col
from ...

Or you could do the same thing with conditional aggregation (which is what pivot uses under the hood anyway):

select
  max(case when name_col = 'a' then time_col end) as time_col_a,
  max(case when name_col = 'a' then length_col end) as length_col_a,
  max(case when name_col = 'b' then time_col end) as time_col_b,
  max(case when name_col = 'b' then length_col end) as length_col_b
from (
  select time_col, length_col, name_col,
    dense_rank() over (partition by name_col order by time_col) as rnk
  from your_table
)
group by rnk
order by rnk;

TIME_COL LENGTH_COL_A TIME_COL LENGTH_COL_B
-------- ------------ -------- ------------
00:01:00            2 00:23:00            3
00:11:22            2                      
01:01:00           45

db<>fiddle

Upvotes: 1

Popeye
Popeye

Reputation: 35930

You can use analytical function ROW_NUMBER and full outer join as follows:

WITH CTE1 AS 
(SELECT T.*, ROW_NUMBER() OVER (ORDER BY LENGTH, TIME) AS RN FROM YOUR_TABLE T WHERE NAME = 'a'),
CTE2 AS 
(SELECT T.*, ROW_NUMBER() OVER (ORDER BY LENGTH, TIME) AS RN FROM YOUR_TABLE T WHERE NAME = 'b')
SELECT A.TIME, A.LENGTH, B.TIME, B.LENGTH
FROM CTE1 A FULL OUTER JOIN CTE2 B
ON A.RN = B.RN

Note: You need to use proper order by to order the records as per your requirement. I have used LENGTH, TIME

Upvotes: 2

Related Questions