CODEWITHSUNDEEP
oracle-databaseoracle11gunpivot
Revious
Revious

Reputation: 8146

Transpose column with rows

I've got a long query that produce some values. The identifier names are too long. And I prefer to have the resultset in 2 columns called "column name" and "column value".

IE for the following query I want:

column name               column value
forn_old_codice_fornitura ***
VOF_VOCE_FATTURABILE_COD  ***
IEF_ASSOGGETTAMENTO_COD   ***

The *** is the actual value.

How can I do?

SELECT 
      forn.forn_old_codice_fornitura,

'Column description 1', VOF.VOF_VOCE_FATTURABILE_COD, 'Column description 2', IEF_ASSOGGETTAMENTO_COD "Cod Assog Neta", 'Column description 3' [...]

Upvotes: 0

Views: 4203

Answers (1)

Taryn
Taryn

Reputation: 247710

Since you are using Oracle 11g, you can use the UNPIVOT function. This takes your columns and converts them to rows:

select col_name , col_value
from
(
  SELECT 
        forn.forn_old_codice_fornitura,
         VOF.VOF_VOCE_FATTURABILE_COD,
         IEF_ASSOGGETTAMENTO_COD,
         ETA.ETA_CODICE_ASSOG,
         CLU.CLU_CLASSE_FORNITURA_COD,
         MVI.CLU_CLASSE_FORNITURA_COD,
         ETA.ETA_USO_CLASSE_FORN,
         MVI.MVI_FLG_SOGGETTO,
         ETA.ETA_FLG_SOGGETTO,
  [...]
) 
unpivot
(
  col_value
  for col_name in (forn_old_codice_fornitura, VOF_VOCE_FATTURABILE_COD,
                   IEF_ASSOGGETTAMENTO_COD, ETA_CODICE_ASSOG, ...)
)

Edit, based on your comment that you want to generate a column description with the long name of the columns, there are two ways that you can do this.

One, you can use a CASE expression that will replace the col_name with the column description that you want, similar to this:

select col_name, 
  col_value,
  case col_name
    when 'col1' then 'Test Col 1'
    when 'col2' then 'Test Col 2'
    when 'col3' then 'Test Col 3'
    when 'col4' then 'Test Col 4'
  end col_desc
from yourtable
unpivot
(
  col_value
  for col_name in (col1, col2, col3, col4)
) unp;

Or you can create a table that contains the conversions from col_name to column description and you will join the table to the unpivot result:

select d.col_name,
  d.col_value,
  cd.col_desc
from
(
  select col_name, 
    col_value
  from yourtable
  unpivot
  (
    col_value
    for col_name in (col1, col2, col3, col4)
  ) unp
) d
inner join cd
  on d.col_name = cd.col_name

See SQL Fiddle with Demo of both

Upvotes: 5

Related Questions