SQL statement that allows me to output multiple columns as one row

Question

I have table in my database that goes like this:

ID | VARIANT | SIFRANT | VALUE

When I call

SELECT * FROM example_table

I get each row on its own. But records in my database can have the same VARIANT. And I'd like to output those records them in the same row. for example if I have

ID | VARIANT | SIFRANT | VALUE
1  | 3       | 5       | 50
2  | 3       | 6       | 49
3  | 3       | 1       | 68

I'd like the output to be

VARIANT | VALUES_5 | VALUES_6 | VALUES_1
3       | 50       | 49       | 68

EDIT: I found the solution using PIVOT, the code goes like this:

select * 
from (
    select variant, VALUE, SIFRANT 
    from example_table
)
pivot 
(
    max(VALUE)
    for SIFRANT  
    in ('1','2','3','4','5','6','7','8','9','10')
)

The general term for this is called Group Concatenation - at least going by MySQL's GROUP_CONTACT() function... I don't really use Oracle so not sure the equiv (in SQL Server this has to be handled via custom logic, there's nothing built in, but the term might give you some direction at least) — jleach, Sep 19 '16 at 13:24
see here: http://stackoverflow.com/questions/16771086/is-there-any-function-in-oracle-similar-to-group-concat-in-mysql — jleach, Sep 19 '16 at 13:25

score 4 · Answer 1 · answered Sep 19 '16 at 13:11

It seems that you only need an aggregation on your data:

with test(ID, VARIANT, SIFRANT, VALUE) as
(
  select 1, 3, 5, 50 from dual union all
  select 2, 3, 6, 49 from dual union all
  select 3, 3, 1, 68 from dual
)
select variant, listagg (value, ' ') within group ( order by id) 
from test
group by variant

SQL statement that allows me to output multiple columns as one row

1 Answers1