data insertion using sql/plsql

Question

I have a base table with below data

table1

NUMBER      TYPE        DATE
1           ABC         2015-05-05 10:00:00
1           XYZ         2015-05-05 11:00:00

I need the output table "test" to have below details

id  T1          TYPE_1  T2          TYPE_2
1   10:00:00    ABC     11:00:00    XYZ

I have tried below, but it is not working. I am a novice to sql plsql.

begin 

if exists (select distinct id from test where id in (select distinct NUMBER from table1))

    begin

        update test set 

        T1 =

        (
            case 
            when TYPE='ABC' then DATE end as T1

        ) ,
        T2 = 
        (
            case
            when TYPE='XYZ' then DATE end as T2
        )  
           where TA = table1.NUMBER
    end

else

    begin

       insert into test (
       T1,
       T2
       )
       select (
       case when TYPE='ABC' then DATE end as T1,
       case when TYPE='XYZ' then DATE end as T2
       )
       from table1 where NUMBER=test.id
    end
end

what if there is more than 1 row per each TYPE per number?

Vamsi Prabhala
– Vamsi Prabhala

2016-05-06 13:03:06 +00:00
Commented May 6, 2016 at 13:03 — Vamsi Prabhala
– Vamsi Prabhala, Commented May 6, 2016 at 13:03
there will not be more than 1 row per each TYPE per number

tester
– tester

2016-05-06 13:14:32 +00:00
Commented May 6, 2016 at 13:14 — tester
– tester, Commented May 6, 2016 at 13:14

APC · Accepted Answer · 2016-05-08 16:10:44Z

What you have there is a so-called upsert: insert or update a row in the target table, depending on whether it matches a row in the source table. In Oracle we can achieve this in pure SQL using a MERGE statement:

merge into test
using ( select abc.number, abc.type as type_1, abc.date as t1
                , xyz.type as type_2, xyz.date as t2
         from table_1 abc
              join table_2 xyz
               on abc.number = xyz.number 
         where abc.type = 'ABC'
         and xyz.type = 'XYZ'
       ) t
on ( test.number = t.number
when not matched then
    insert (t.number, t.type_1, t.t1, t.type_2, t.t2)
when matched then
    update set test.t1 = t.t1
              , test.t2 = t.t2
/

The sub-query in the USING clause pivots tow rows into one using the logic inferred from your desired output. You may need to extend this query, depending on how much you simplified your scenario to post it here.

valex · Accepted Answer · 2016-05-06 13:57:25Z

0

Try this if only 'ABC' an 'XYZ' types exist in the table:

SELECT number as id,
       MAX(CASE WHEN Type = 'ABC' THEN Date ELSE NULL END) as T1,
       MAX('ABC') as Type_1,
       MAX(CASE WHEN Type = 'XYZ' THEN Date ELSE NULL END) as T2,
       MAX('XYZ') as Type_2
FROM T   
GROUP BY Number

answered May 6, 2016 at 13:57

valex

24.2k7 gold badges47 silver badges62 bronze badges

3 Comments

tester Over a year ago

there are lakhs of records, hence it is taking way too much time to give output.

tester Over a year ago

and it is inserted in a new line . I needed an output in a single line/record

tester Over a year ago

and we get an error "ORA-00937" not a single-group group function

Collectives™ on Stack Overflow

data insertion using sql/plsql

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related