locked
Add a new column to the destinations table RRS feed

  • Question

  • hi,

    my sql  source table having following data 

    empid ename phonenumber

    1 a 123456

    1 a 234567

    2 b 890765

    2 b 765432

    3 c 134256

    -------------------------------------

    now the above source table convert to the  data into the destination tables as show below

    empid ename phonenumber1 phonenumber2

    1 a 123456 23456

    2 b 890765 765432

    3 c 134256 unknow

     ------------------------------------------------------------

    psl give the replay to me 

    Wednesday, August 29, 2012 5:28 AM

Answers

  • Assuming you have only two numbers 

    declare @emp table (empid int, ename char (1),phn bigint)
    insert into @emp values(1,'a',  123456),
    (1 ,'a',234567),
    (2 ,'b',890765),
    (2 ,'b',765432),
    (3 ,'c',134256);
    
    with CTE as(select *, ROW_NUMBER()OVer(partition by Empid order by phn)RN from @emp e),
    Num1 as (select * from CTE where Rn=1),Num2 as (select * from CTE where Rn=2)
    select n1.empid,n1.ename,N1.phn,ISNULL(cast(N2.phn as varchar(15)),'Unknown')from Num1 N1 left Join Num2 N2 on N1.empid=N2.empid

    Regards
    Satheesh

    Wednesday, August 29, 2012 6:42 AM

All replies

  • Assuming you have only two numbers 

    declare @emp table (empid int, ename char (1),phn bigint)
    insert into @emp values(1,'a',  123456),
    (1 ,'a',234567),
    (2 ,'b',890765),
    (2 ,'b',765432),
    (3 ,'c',134256);
    
    with CTE as(select *, ROW_NUMBER()OVer(partition by Empid order by phn)RN from @emp e),
    Num1 as (select * from CTE where Rn=1),Num2 as (select * from CTE where Rn=2)
    select n1.empid,n1.ename,N1.phn,ISNULL(cast(N2.phn as varchar(15)),'Unknown')from Num1 N1 left Join Num2 N2 on N1.empid=N2.empid

    Regards
    Satheesh

    Wednesday, August 29, 2012 6:42 AM
  • as the above problem can solve using SSIS

    psz give replay


    sreekanth kancharla

    Wednesday, August 29, 2012 7:14 AM