0

I have the following table:

 |fk ID|value | val id|
 |  1  | val11|  1    |
 |  1  | val12|  2    |
 |  2  | val21|  1    |
 |  2  | val22|  2    |

I want to transpose it to:

 | fk ID | val1Title | val2Title |
 | 1     | val11     | val12     |
 | 2     | val21     | val22     |

Ive added an ID column to the top table which is used to order so we know that val11 is in the first column because it has an id less that val12.

I have an oracle DB and I think I need to pivot. I've found a couple of articles on it (e.g. Transposing Table) although it seems to be a lot of work for the pivot. Is this the easiest way?

How can I easily transpose? Im using Oracle 11g so am happy to use Oracle specific functions. I've used ListAgg but that combines the columns. I want to columns separate though.

EDIT: The design of the original table is not normalised at the moment because this is actually the result of quite a complicated query.

Community
  • 1
  • 1
RNJ
  • 15,272
  • 18
  • 86
  • 131
  • You cant use pivot because the values are mixed. – Mihai Apr 23 '14 at 20:32
  • :( is there anything I can do then? – RNJ Apr 23 '14 at 20:38
  • Probably you can,but this is beyond my knowledge.The table design is bad.If you dont get an answer put a bounty on it. – Mihai Apr 23 '14 at 20:39
  • 1
    Do you know that there will be at most 2 `value` values for each `fk_id` value? So your end result will always have 3 columns? Or do you want the number of columns in your output to change if there are more rows in your table for a particular `fk_id`? How do you know which `value` goes in `val1Title` and which goes in `val2Title`? Is that based on alphabetic order? – Justin Cave Apr 23 '14 at 20:49
  • Hi @JustinCave Thanks for the answer. Yes I can assume there will be two values. (there may be more but they will always be consistent and I can update the sql accordingly). The order is actually based on an internal id. So in the example val11 has an id before val12 and val21 has an id before val22. Thanks – RNJ Apr 23 '14 at 21:06
  • Ive updated the question with an ID to make it a bit clearer hopefully – RNJ Apr 23 '14 at 21:08

1 Answers1

2

You can do an old-school pivot

SELECT fk_ID,
       MAX(CASE WHEN val_id=1 THEN value ELSE null END) as Val1Title,
       MAX(CASE WHEN val_id=2 THEN value ELSE null END) as Val2Title
  FROM table_name
 GROUP BY fk_ID

In 11g, you can also use the PIVOT keyword

SELECT fk_id,
       "1_VAL" as Val1Title,
       "2_VAL" as Val2Title
  FROM table_name
 PIVOT( MAX(value) as val for (val_id) in (1,2))

See this sqlfiddle for an example

Justin Cave
  • 227,342
  • 24
  • 367
  • 384