September 21, 2005 at 11:50 pm
Hi All,
I have 2 tables T1 & T2.
Data in T1 is as follows :
Ln_no b_flag Pkt_type pkt_id
1 'A' 0 1
1 'A' 0 2
2 'A' 1 1
2 'A' 1 2
1 'A' 3 1
1 'A' 3 2
2 'A' 3 3
2 'A' 3 4
Now i have 2 insert this data into another table T2.
Ln_no b_flag 0_pkt 1_pkt 2_pkt 3_pkt 4_pkt
1 'A' 1,2 NULL NULL 1,2 NULL
2 'A' NULL 1,2 NULL 3,4 NULL
From table T1 data is inserted into table T2 based on the following logic:
For a particular combination of Ln_No and b_flag for ex. 1 and 'A',
one has combination of 0 pkt_type with pkt_ids 1&2, and 3 pkt_type - 1&2
Now one row is inserted into T2 corresponding to a unique combination of
Ln_no & b_flag...and the pkt ids corresponding to particular pkt_type are
stored comma seperated...with 0_pkt correspondign to pkt_type 0,
1_pkt corresp. to pkt_type 1 and so on so forth.
In my logic i have to do a lot of looping to get the above results.
Can someone pls suggest me a more effiecient way of transferring data
from T1 TO T2 based on the above logic.
Thanx in advance,
Rgds,
Rajesh
September 22, 2005 at 12:30 am
September 22, 2005 at 12:47 am
Hi Hari,
Pkt_type is fixed from 0 to 5
whereas pkt_id can be anything but in real time it will be from 1 to say max. 50...
September 22, 2005 at 2:06 am
Try this - the varchar(20) in the function can easily be changed to something else, e.g varchar(8000).
create table T1(Ln_no int, b_flag char(1), Pkt_type int, pkt_id int)
go
insert T1 select 1, 'A', 0, 1
insert T1 select 1, 'A', 0, 2
insert T1 select 2, 'A', 1, 1
insert T1 select 2, 'A', 1, 2
insert T1 select 1, 'A', 3, 1
insert T1 select 1, 'A', 3, 2
insert T1 select 2, 'A', 3, 3
insert T1 select 2, 'A', 3, 4
go
create function comb
(
@Ln_no int,
@b_flag char(1),
@Pkt_type int
)
returns varchar(20)
as
begin
declare @STR varchar(20)
select @STR = ''
select @STR = @STR + case when @STR = '' then '' else ',' end + cast(pkt_id as varchar)
from T1 where Ln_no = @Ln_no and b_flag = @b_flag and Pkt_type = @Pkt_type order by pkt_id
return case when @STR = '' then NULL else @STR end
end
go
select Ln_no, b_flag, dbo.comb(Ln_no, b_flag, 0), dbo.comb(Ln_no, b_flag, 1), dbo.comb(Ln_no, b_flag, 2), dbo.comb(Ln_no, b_flag, 3), dbo.comb(Ln_no, b_flag, 4)
from T1 group by Ln_no, b_flag
drop function comb
go
drop table T1
go
September 22, 2005 at 2:15 am
Hi Rajesh,
This is the efficient solution that I can think of coz i used only one loop. if anyone else can come out with a better one, I'll happy to know.
~hari
-----------------------------------------------------------------------
--Step 1
create table #t1
(
Ln_no int,
b_flag char(1),
Pkt_type int,
pkt_id int
)
insert into #t1 values (1, 'A', 0, 1)
insert into #t1 values (1, 'A', 0, 2)
insert into #t1 values (2, 'A', 1, 1)
insert into #t1 values (2, 'A', 1, 2)
insert into #t1 values (1, 'A', 3, 1)
insert into #t1 values (1, 'A', 3, 2)
insert into #t1 values (2, 'A', 3, 3)
insert into #t1 values (2, 'A', 3, 4)
--Step 2
Create table #t2
(
[id] int identity(1,1),
Ln_no int,
b_flag char(1),
_0_pkt varchar(250),
_1_pkt varchar(250),
_2_pkt varchar(250),
_3_pkt varchar(250),
_4_pkt varchar(250)
)
--step 3
insert into #t2 (ln_no,b_flag) select distinct ln_no,b_flag from #t1
--step 4
update #t2 set _0_pkt = null, _1_pkt = null, _2_pkt = null, _3_pkt = null, _4_pkt = null
declare @tmp_ln_no int, @tmp_b_flag char(1),@pkt_str varchar(250), @max_id int,@cur_id int
Select @max_id = IDENT_CURRENT('#t2'),@cur_id=1
while @cur_id <= @max_id
Begin
Select @tmp_ln_no = ln_no, @tmp_b_flag = b_flag from #t2 where [id] = @cur_id
--0_pkt
Select @pkt_Str = ''
Select @pkt_str = @pkt_str + cast(pkt_id as varchar(3)) + ','
From #t1 Where ln_no = @tmp_ln_no And b_flag = @tmp_b_flag
and pkt_type=0
update #t2
Set _0_pkt = @pkt_Str
Where ln_no = @tmp_ln_no and b_flag = @tmp_b_flag
commit transaction
--1_pkt
Select @pkt_Str = ''
Select @pkt_str = @pkt_str + cast(pkt_id as varchar(3)) + ','
From #t1 Where ln_no = @tmp_ln_no And b_flag = @tmp_b_flag
and pkt_type=1
update #t2
Set _1_pkt = @pkt_Str
Where ln_no = @tmp_ln_no and b_flag = @tmp_b_flag
commit transaction
--2_pkt
Select @pkt_Str = ''
Select @pkt_str = @pkt_str + cast(pkt_id as varchar(3)) + ','
From #t1 Where ln_no = @tmp_ln_no And b_flag = @tmp_b_flag
and pkt_type=2
update #t2
Set _2_pkt = @pkt_Str
Where ln_no = @tmp_ln_no and b_flag = @tmp_b_flag
--3_pkt
Select @pkt_Str = ''
Select @pkt_str = @pkt_str + cast(pkt_id as varchar(3)) + ','
From #t1 Where ln_no = @tmp_ln_no And b_flag = @tmp_b_flag
and pkt_type=3
update #t2
Set _3_pkt = @pkt_Str
Where ln_no = @tmp_ln_no and b_flag = @tmp_b_flag
--4_pkt
Select @pkt_Str = ''
Select @pkt_str = @pkt_str + cast(pkt_id as varchar(3)) + ','
From #t1 Where ln_no = @tmp_ln_no And b_flag = @tmp_b_flag
and pkt_type=4
update #t2
Set _4_pkt = @pkt_Str
Where ln_no = @tmp_ln_no and b_flag = @tmp_b_flag
Set @cur_id = @cur_id + 1
End
select * from #t2
drop table #t1
drop table #t2
-----------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply