terça-feira, 2 de julho de 2013

Converting Rows to Columns (pivoting) in PL/SQL,

I thought it was pretty easy... but, after reading google and finding 10 different solutions the easiest one was:

 SELECT distinct row_nr
      ,MAX(DECODE(COL_NR, 1, o.NUMBER_VAL)) stat_1
      ,MAX(DECODE(COL_NR, 2, o.NUMBER_VAL)) stat_2
      ,MAX(DECODE(COL_NR, 3, o.NUMBER_VAL)) stat_3
      ,MAX(DECODE(COL_NR, 4, o.NUMBER_VAL)) stat_4
      ,MAX(DECODE(COL_NR, 5, o.NUMBER_VAL)) stat_5
      ,MAX(DECODE(COL_NR, 6, o.NUMBER_VAL)) stat_6
    ,MAX(DECODE(COL_NR, 7, o.NUMBER_VAL)) stat_7
     ,MAX(DECODE(COL_NR, 8, o.NUMBER_VAL)) stat_8
     ,MAX(DECODE(COL_NR, 9, o.NUMBER_VAL)) stat_9
     ,MAX(DECODE(COL_NR, 10, o.NUMBER_VAL)) stat_10
      ,MAX(DECODE(COL_NR, 11, o.NUMBER_VAL)) stat_10
    from table( as_read_xlsx.read( as_read_xlsx.file2blob( 'WORKDIR', 'dummy.xlsx' ),'Sheet1' ) ) o
    where Sheet_nr=1 and row_nr>1 
    group by row_nr
    order by  row_nr;

P.S- Also reading from a *.XLSX... but for a table it would be the same.

Sem comentários: