Add a new column to the destinations table

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 

Answer 1

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

Answer 2

as the above problem can solve using SSIS

psz give replay

---

sreekanth kancharla