-1

I have data like below

ROW_ID    Col0           Col1    Col2    Col3    
1         05/22/2020      123     ABC     1
2         05/12/2020      DEF     1       2
3         06/13/2020      PRR     N1      4

I am looking for the output where data will transformed very little and then will be un-pivoted as shown below

ROW_ID  COLUMN_NAME  VALUE
1       Col0         05/22/2020
1       Drv_Col0     May-2020
1       Col1         123
1       Col2         ABC
1       Col3         1
1       Sum_Col3     3
2       Col0         05/12/2020
2       Drv_Col0     May-2020
2       Col1         DEF
2       Col2         1
2       Col3         2
2       Sum_Col3     3
3       Col0         06/13/2020
3       Drv_Col0     Jun-2020
3       Col1         PRR
3       Col2         N1
3       Col3         4
3       Sum_Col3     4
Koushik Chandra
  • 1,565
  • 12
  • 37
  • 73
  • Take a look at the "standard SQL unpivot" answer here [Unpivot with column name](https://stackoverflow.com/questions/19055902/unpivot-with-column-name) – Charles May 27 '21 at 17:10

1 Answers1

0

You can use a lateral join. Assuming that the columns all have the same type:

select t.row_id, v.*
from t cross join lateral
     (values ('Col0', col0),
             ('Drv_Col0', to_char(col0, 'MON-YYYY'),
             ('Col1', col1),
             ('Col2', col2),
             ('Col3', col3),
             ('Sum_Col3', ???)
     ) v(column_name, value);

Note: You may need to cast the columns so they are all strings.

The question does not specify how sum_col3 is defined and the definition is not obvious. But some expression can go there.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • How you are handling the SUM for Col3 – Koushik Chandra May 27 '21 at 17:33
  • @KoushikChandra . . . I didn't even see it in the question. That said, the question is no clear on how it is defined, so I don't know what the expression is. – Gordon Linoff May 27 '21 at 21:22
  • I am getting error - data type of corresponding columns are not compatible. For my example Col0 is DATE and Col3 is BIGINT – Koushik Chandra May 27 '21 at 21:54
  • 1
    @KoushikChandra . . . The answer specifies that you may need to cast the values to a common type, such as a string. Your question doesn't specify what the types are, so you can figure out what the right method is to convert to a string. – Gordon Linoff May 27 '21 at 23:58