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
– jleach, Commented Sep 19, 2016 at 13:24

Aleksej · Accepted Answer · 2016-09-19 13:11:33Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related