# 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

Wednesday, August 29, 2012 5:28 AM

• 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