Grouping multiple rows from a table into column

Question

I have two table as below.

Table 1

+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 |
+------+------+------+------+
|    1 | 1.5  | 1.5  | 2.5  |
|    1 | 2.5  | 3.5  | 1.5  |
+------+------+------+------+

Table 2

+------+--------+
| Col1 |  Col2  |
+------+--------+
|    1 |  12345 |
|    1 | 678910 |
+------+--------+

I want the result as below.

+------+------+------+------+-------+--------+
| Col1 | Col2 | Col3 | Col4 | Col5  |  Col6  |
+------+------+------+------+-------+--------+
|    1 |    4 |    5 |    4 | 12345 | 678910 |
+------+------+------+------+-------+--------+

Here Col2, Col3 and Col4 is the aggregate of value from Col2,3,4 in Table 1. And rows from Table 2 are transposed to Columns in the result.

I use Oracle 11G and tried the PIVOT option. But I couldn't aggregate values from Column 2,3,4 in Table 1.

Is there any function available in Oracle which provides direct solution without any dirty work around?

Thanks in advance.

Answer 1

Since you will always have only 2 records in second table simple grouping and join will do. Since I dont have tables I am using CTEs and Inline views

with cte1 as (
    select 1 as col1 , 1.5 as col2 , 1.5 as col3, 2.5 as col4 from dual
    union all
    select 1  , 2.5  , 3.5 , 1.5 fom dual
    ) ,
    cte2 as (
    select 1 as col1 , 12345 as col2  fom dual
    union all
    select 1,678910  fom dual )

    select* from( 
    (select col1,sum(col2) as col2 , sum(col3) as col3,sum(col4) as col4
       from cte1 group by col1) as x 
    inner join 
    (select col1  ,min(col2) as col5 ,max(col2) as col from cte2
    group by col1
    ) as y
    on x.col1=y.col1)