CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Palan
Palan

Reputation: 1

How to convert dynamic columns into rows in Oracle 11g?

I am trying to convert dynamic columns into row in Oracle 11g. I know it can possible using unpivot, but only pre-exists columns not with dynamic columns.

Data Input:

select 'Bulding1', 'Building2', 'Building3', 'Building4', 'Building5','Building6' 
from dual;

Expected output:

Column_Name
-----------
- Building1
- Building2
- Building3
- Building4
- Building5
- Building6
- Building7

Upvotes: 0

Views: 1280

Answers (1)

MT0
MT0

Reputation: 167822

Use UNION ALL:

SELECT 'Building1' AS column_name FROM DUAL
UNION ALL
SELECT 'Building2' FROM DUAL
UNION ALL
SELECT 'Building3' FROM DUAL
UNION ALL
SELECT 'Building4' FROM DUAL
UNION ALL
SELECT 'Building5' FROM DUAL
UNION ALL
SELECT 'Building6' FROM DUAL;

or use UNPIVOT:

SELECT column_name
FROM   (
  SELECT 'Building1',
         'Building2',
         'Building3',
         'Building4',
         'Building5',
         'Building6' 
  FROM   DUAL
)
UNPIVOT(
  column_name FOR name IN (
    "'BUILDING1'",
    "'BUILDING2'",
    "'BUILDING3'",
    "'BUILDING4'",
    "'BUILDING5'",
    "'BUILDING6'"
  )
);

Upvotes: 1

Related Questions