Skip to Main Content
  • Questions
  • Transforming multiple rows into a single row with multiple columns, without aggregation

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Vjekoslav.

Asked: January 21, 2020 - 2:53 pm UTC

Answered by: Chris Saxon - Last updated: October 15, 2020 - 10:48 pm UTC

Category: SQL - Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hello,
Is it possible to write a single SQL query that would concatenate multiple rows into a single row with multiple columns?
For example, I have a table with the following data:

TYPE    DATE        AMOUNT
--------------------------------
B       30.6.2019   15
B       30.11.2019  20
C       22.12.2019  17


And I want a query that would return a single row:

TYPE1 DATE1     AMOUNT1 TYPE2 DATE2      AMOUNT2 TYPE3 DATE3      AMOUNT3
-------------------------------------------------------------------------
B     30.6.2019 15      B     30.11.2019 20      C     22.12.2019 17


The column names don't really matter, I just need to combine the original three rows into a single row with multiple columns, so I can't use concatenation.

I know that there will always be 3 rows in the original table, so there's not going to be a million columns in the final result.
For performance reasons, I would like to avoid using PL/SQL if at all possible, and just do it in a single query, but I'm not sure if it's possible.

Thank you!

and we said...

Sounds like you need to PIVOT!

Assign each row a unique number (e.g. with row_number()) first, then pivot the values:

with rws as (
  select 'B' type, to_date ( '30.6.2019 ', 'dd.mm.yyyy' ) dt, 15 num from dual union all
  select 'B' type, to_date ( '30.11.2019', 'dd.mm.yyyy' ) dt, 20 num from dual union all
  select 'C' type, to_date ( '22.12.2019', 'dd.mm.yyyy' ) dt, 17 num from dual
), rns as (
  select row_number () over ( order by type ) rn,
         r.*
  from   rws r
)
  select * from rns
  pivot (
    min ( type ) tp, min ( dt ) dt, min ( num ) 
    for rn in ( 1, 2, 3 )
  );
  
1_TP   1_DT                    1   2_TP   2_DT                    2    3_TP   3_DT                   3    
B      30-JUN-2019 00:00:00   15   B      30-NOV-2019 00:00:00   20    C      22-DEC-2019 00:00:00  17 


Read more at:

https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot

and you rated our response

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

to convert multiple rows into a single row

October 14, 2020 - 1:49 pm UTC

Reviewer: Faith from UK

Hello,
Is it possible to write a single SQL query that would concatenate multiple rows into a single row with multiple columns?
For example, I have a table with the following data:

TYPE DATE AMOUNT
--------------------------------
B 30.6.2019 15
B 30.11.2019 20
C 22.12.2019 17


And I want a query that would return a single row, the response given was perfectly fine but can this be dynamic instead of specifying the values in the with clause.:

TYPE1 DATE1 AMOUNT1 TYPE2 DATE2 AMOUNT2 TYPE3 DATE3 AMOUNT3
-------------------------------------------------------------------------
B 30.6.2019 15 B 30.11.2019 20 C 22.12.2019 17

Chris Saxon

Followup  

October 14, 2020 - 4:39 pm UTC

What exactly have you tried? Show us your SQL!

to convert multiple rows into a single row

October 15, 2020 - 4:25 pm UTC

Reviewer: Faith from UK

Hi there,
Please can you kindly removed the first query as I cannot do remove it.

Thank you for trying to help. I have managed to solve it.


Connor McDonald

Followup  

October 15, 2020 - 10:48 pm UTC

Done

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.