Return values from multiple rows as columns

Question

Is it possible to return multiple row values, per same id, as a column?

If my table is:

ID     | Value  |Column_data
--------------------------------  
1      | a      |  DATA1
1      | b      |  DATA1
2      | c      |  DATA2 
2      | x      |  DATA2 
3      | y      |  DATA3 
3      | z      |  DATA3

(Each Id has always 2 values)

The select should return:

1,a,b,DATA1
2,c,x,DATA2
3,y,z,DATA3

You're after a crosstab. Lookup 'crosstab' and you'll find a million examples. — Nick.Mc, Feb 12 '13 at 09:35
@dasblinkenlight I don't need to count them I need to get them — yuris, Feb 12 '13 at 09:38

score 1 · Answer 1 · answered Feb 12 '13 at 09:38

1

Or listagg(col2) over (...) in 11g

answered Feb 12 '13 at 09:38

Plouf

627
3
7

score 1 · Accepted Answer · answered Feb 12 '13 at 10:18

You did not state what version of Oracle you are using but if you are using Oracle 11g+, then you can transform this data into columns using the PIVOT function:

select id,
  C1,
  C2,
  column_data
from
(
  select id, value, column_data,
    row_number() over(partition by id order by id, value) rn
  from yourtable
) 
pivot
(
  max(value)
  for rn in ('1' as C1, '2' as C2)
) 
order by id

See SQL Fiddle with Demo.

Prior to Oracle 11g, you could use an aggregate function with a CASE expression to transform the rows into columns:

select id,
  max(case when rn = 1 then value end) C1,
  max(case when rn = 2 then value end) C2,
  column_data
from
(
  select id, value, column_data,
    row_number() over(partition by id order by id, value) rn
  from yourtable
) 
group by id, column_data
order by id

See SQL Fiddle with Demo

The result of both queries is:

| ID | C1 | C2 | COLUMN_DATA |
------------------------------
|  1 |  a |  b |       DATA1 |
|  2 |  c |  x |       DATA2 |
|  3 |  y |  z |       DATA3 |

thanks, the version prior to Oracle 11g – yuris Feb 12 '13 at 10:22 — yuris, Feb 12 '13 at 10:22

score 0 · Answer 3 · answered Feb 12 '13 at 09:34

0

You could use pivot, have a look here:

http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

answered Feb 12 '13 at 09:34

score 0 · Answer 4 · answered Feb 12 '13 at 10:10

You can try something like this:

with t as ( select id, Column_data, xmlagg(xmlelement("e", Value)) xl 
from table1
group by id, Column_data)
select id, 
       extract(xl, 'e[1]/text()').getstringval() c1, 
       extract(xl, 'e[2]/text()').getstringval() c2, 
       Column_data
from t

Here is a sqlfiddle demo

Return values from multiple rows as columns

4 Answers4