CODEWITHSUNDEEP
sqloracle-database
Drew13
Drew13

Reputation: 1371

Pivot columns into rows

I have a query that selects 5 columns (d, p, name, val1, val2):

SELECT
  table.bd AS d,
  g.p AS p,
  g.name || '_' || g.class || '_' || g.gname AS name,
  (CASE
    WHEN
      table.type = 'D'
    THEN
      table.b
    ELSE
      0
  END) AS VAL1,
  (CASE
    WHEN
      table.type = 'S'
    THEN
      table.b
    ELSE
      0
  END) AS VAL2

I need my rows to be split into 2 separate rows. Both rows would have the first 3 columns, but one would contain only a val1 column and the other would contain a val2 column.

For example, if my query returns a row:

  d    p    name    val1    val2
'val' 25   'john'   450     90

I need:

  d    p    name    val1
'val' 25   'john'   450

  d    p    name    val2
'val' 25   'john'   90

Is there a way to do this?

Upvotes: 0

Views: 48

Answers (1)

Vladislav Molitvik
Vladislav Molitvik

Reputation: 11

Set operators (UNION / UNION ALL) shall do the trick. Cannot see the structure of data in full, but below shall work:

SELECT d, p, name, val1 FROM table UNION ALL select d, p , name, val2 FROM table;

Use UNION instead of UNION ALL if you don't need duplicated results in case val1 = val2.

SELECT d, p, name, val1 FROM table UNION select d, p , name, val2 FROM table;

However, as it will be a single column for val1 and val2 it will have a name val1 as it comes from first select statement, so the result will be as:

  d    p    name    val1
'val' 25   'john'   450
'val' 25   'john'   90

Upvotes: 1

Related Questions