7 Replies Latest reply: May 29, 2012 5:19 AM by user in RSS

    Combine two columns into one?

    924823
      I have a table which has two columns called manager_task and worker_task among others. The requirement is such that, I need to combine values from both these columns into one based on following rules:

      1. if manager_task = worker_task, append the values with '-Manager' and '-Worker' appropriately
      2. if manager_Task <> worker_task, show them as it is

      I came up with a query containing unions, but I am looking for a better approach

      select manager_task ||' - Manager' task_name
      from TABLE_A
      where flow_id=12345
      and worker_task = manager_task
      union
      select manager_task task_name
      from TABLE_A
      where flow_id=12345
      and worker_task <> manager_task
      union
      select worker_task||' - Worker' task_name
      from TABLE_A
      where flow_id=12345
      and worker_task = manager_task
      union
      select wkr_task_name task_name
      from TABLE_A
      where flow_id=12345
      and worker_task <> manager_task
        • 1. Re: Combine two columns into one?
          908002
          select decode(worker_task,manager_task,manager_task ||' - Manager',manager_task) task_name
          from TABLE_A
          where flow_id=12345
          and worker_task = manager_task
          union
          select decode(worker_task,manager_task,worker_task ||' - worker',worker_task) task_name
          from TABLE_A
          where flow_id=12345
          and worker_task = manager_task
          • 2. Re: Combine two columns into one?
            929901
            Select CASE MGR_TSK
            When WRKR_TSK THEN manager_task ||' - Manager'
            Else MGR_TSK||TSK_NM
            End as MGR_TSK
            Union
            Select Case WRKR_TSK
            When MGR_TSK THEN wrkr_task ||' -Worker'
            Else wrkr_TSK||TSK_NM
            End as Wrkr_TSK


            Thanks,

            Edited by: pandurang_sp on May 29, 2012 1:31 AM
            • 3. Re: Combine two columns into one?
              indra budiantho
              /* Formatted on 2012/05/29 15:23 (Formatter Plus v4.8.8) */
              SELECT CASE
                       WHEN worker_task = manager_task
                         THEN manager_task || ' - Manager ' || worker_task || ' - Worker'
                       ELSE manager_task || '  ' || worker_task
                     END task_nm
                FROM table_a
               WHERE flow_id = 12345
              • 4. Re: Combine two columns into one?
                436317
                with table_a as (select 12345 flow_id, '1' manager_task, '2' worker_task from dual
                                        union
                                        select 12345 flow_id, '1' manager_task, '1' worker_task from dual
                                        union
                                        select 12345 flow_id, '2' manager_task, '1' worker_task from dual)
                select case when manager_task = worker_task then manager_task||'-Manager' 
                                else manager_task
                                end case,
                         case when manager_task = worker_task then manager_task||'-Worker' 
                                else worker_task
                                end case
                from table_a;
                • 5. Re: Combine two columns into one?
                  Siddharth Singh
                  You can try below logic. It will give you inline result as well as performance will be better

                  select e.sal ||' '|| nvl(x.comm,0) from emp e,(select comm, empno from emp1) x where e.empno=x.empno
                  • 6. Re: Combine two columns into one?
                    924823
                    Thank you all for your reply. Great help!
                    • 7. Re: Combine two columns into one?
                      user in
                      create table mw(manager_task varchar2(10),wt varchar2(10));

                      insert into mw values('audit','audit');
                      insert into mw values('playing','checking');
                      insert into mw values('collect','collect');
                      insert into mw values('measure','measure');
                      insert into mw values('checking','remove');

                      declare
                      cursor cr is select mt,wt from mw;
                      m varchar2(10);
                      t varchar2(10);
                      begin
                      open cr;
                      loop
                      fetch cr into m,t;
                      if m=t then
                      m:=m||'-m';
                      t:=t||'-t';
                      dbms_output.put_line(m||' '||t);
                      else
                      m:=m;
                      t:=t;
                      dbms_output.put_line(m||' '||t);
                      end if;
                      exit when cr%notfound;
                      end loop;
                      close cr;
                      end;
                      /