Search code examples
sqloracle-databaseunpivot

Columns data into rows in oracle

enter image description here

Data is present in the screenshot as table format. I want to convert into desired format as mentioned below.

Table describe :

Table A
(branch_code, branch_name, branch_state, hol1, hol2, hol3....hol100)

Expected Output

TAMF046  14/01/2021
TAMF046  15/01/2021
TAMF046  26/01/2021
KERF047  26/01/2021
KERF047  11/03/2021
KERF047  02/04/2021
Solution

  • This is exactly what UNPIVOT is for

    with t(id, c1, c2, c3) as (
  select 1, 'a', 'b', 'c' from dual union all
  select 2, 'aa', 'bb', 'cc' from dual
)
select *
from t
unpivot (
  val
  for col in (
    c1 as 'A1',
    c2 as 'A2',
    c3 as 'A3'
  )
)

    val is the new column that will contain values from columns c1 c2 c3.

    col is the new column that will contain the name of the column from where the val comes from.

    A1 A2 A3 are the values you want to fill in the col for each unpivoted column (these aliases can be omitted if you are ok with the original column names).