0

I have the data as follows:-

Item_ID Activity

1 "In-Store"

1 "Online"

1 "Mall"

1 "Shops"

2 "Flagship"

2 "Destination"

And I want to convert the rows into columns as follows:-

Item_ID Column_1 Column_2 Column_3 Column_4

1 "In-Store" "Online" "Mall" "Shops"

2 "Flagship" "Destination"

Please can someone guide me how to do the above in Oracle SQL? I have tried the PIVOT function but it requires static (hardcoded) list of values.

An Item can have upto 4 activities but the value in activity can be anything (I have 135 unique values in the Activity column and the activity list is dynamic).

Rameez
  • 3
  • 1

1 Answers1

0

You could assign a rank/row number to each activity for each item ID:

select item_id, activity,
  row_number() over (partition by item_id order by activity) as rn
from your_table

and then use that value for the pivot:

select *
from (
  select item_id, activity,
    row_number() over (partition by item_id order by activity) as rn
  from your_table
)
pivot (max(activity) for (rn) in (1, 2, 3, 4))
ITEM_ID 1 2 3 4
1 In-Store Mall Online Shops
2 Destination Flagship

db<>fiddle

The actual activity values don't matter, the pivot is only based on the numeric value. That also makes it easy to show a fifth column if needed - just add 5 to the list.

If the order the activities appears for each item matters then you'll need to specify that in the order by clause; from just the two columns you gave there isn't anything else to order by so I've put them in alphabetic order, but you might have other rules and data that lets you do some other order.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Hi Alex: This is very useful. The ITEM_ID 2 has only 2 activities but with the SQL it is assigning Destination to column 1 and Flagship to column 2, 3 and 4. How can we keep column 3 and 4 as NULL for ITEM_ID=2? Many thanks!!!! – Rameez Jul 07 '21 at 13:12
  • It isn't doing that - you can see the result above and in the fiddle. Maybe your data has duplicate activities; or your version has a join in the inner query and that is producing more rows than you think? Using `dense_rank` instead of `row_number` should solve it though. – Alex Poole Jul 07 '21 at 13:18
  • Thank you so much, Alex!! The above SQL answer resolves the question. – Rameez Jul 07 '21 at 13:32