|
Subject:
|
Making 3 rows become 1 row in another table
|
|
Posted By:
|
mcinar
|
Post Date:
|
10/7/2004 7:27:15 PM
|
Hi All,
Each unique ID in my table has 3 rows.
Table Name: Table_1
Columns: Table_1_ID int,
Date_CH char(10),
Amnt_IN int
Rows:
Table_1_ID Date_CH Amnt_IN
1 01/01/04 100
1 02/02/04 200
1 02/02/04 300
2 04/04/04 400
2 05/05/04 500
2 05/05/04 600
.
.
.
What is the best way making inserting these records into another table
as;
Table Name: Table_2
Columns: Table_2_ID int
Date1_CH char(10),
Date2_CH char(10),
Date3_CH char(10),
Amnt1_IN int,
Amnt2_IN int,
Amnt3_IN int,
Table_2_ID Date1_CH Amt1_IN Date2_CH Amt2_IN Date3_CH Amnt3_IN 1 01/01/04 100 02/02/04 200 03/03/04 300
2 04/04/04 400 05/05/04 500 06/06/04 600
Thank you in advance.
|
|
Reply By:
|
happygv
|
Reply Date:
|
10/7/2004 11:00:40 PM
|
Not sure if this is going to help in your case, as the data in Date_CH and Amnt_IN are dynamic.
http://support.microsoft.com/default.aspx?kbid=175574#3
But this should be worth done, using frontend apps.
Cheers!
_________________________
- Vijay G
Strive for Perfection
|
|
Reply By:
|
r_ganesh76
|
Reply Date:
|
10/8/2004 12:06:45 AM
|
Since you dont have a primary key column for the table, you may need to write a stored procedure to achieve the task
Regards
Ganesh
|
|
Reply By:
|
mcinar
|
Reply Date:
|
10/8/2004 6:19:32 AM
|
Hi happygv,
that article will be very helpful.
As you mentioned that the frontend apps will be more practical, but I need a logic in the stored procedure, because I am using Crystal Report.
Thanks for your help.
|
|
Reply By:
|
mcinar
|
Reply Date:
|
10/8/2004 6:21:20 AM
|
Hi Ganesh,
I have a primary key and I need to make it happen in the stored procedure.
Thanks for your help.
|
|
Reply By:
|
happygv
|
Reply Date:
|
10/8/2004 10:09:45 PM
|
Are you sure for every Table_1_ID in Table_1 there would be three rows ALWAYS?
_________________________
- Vijay G
Strive for Perfection
|
|
Reply By:
|
happygv
|
Reply Date:
|
10/8/2004 10:27:47 PM
|
Hi mcinar,
If so(every ID would have 2 rows each for sure), this should be what you were looking for.Create Procedure InsertTable2
as
Declare @Id int, @Date char(10), @Amnt int, @tmpId int, @Cnt int
Declare @Date1_CH char(10), @Date2_CH char(10), @Date3_CH char(10)
Declare @Amt1_IN int, @Amt2_IN int, @Amt3_IN int
Declare Tbl1 cursor for Select * from table_1 order by Table_1_Id
Open Tbl1
Fetch next from Tbl1 into @Id, @Date, @Amnt
Set @Cnt = 1
While @@FETCH_STATUS = 0
BEGIN
If @tmpId is NULL or @tmpId <> @Id
BEGIN
Set @tmpId = @Id
Set @Cnt = 1
END
Else
BEGIN
Set @Cnt = @Cnt + 1
END
If @Cnt=1
Select @Date1_CH=@Date, @Amt1_IN=@Amnt
If @Cnt=2
Select @Date2_CH=@Date, @Amt2_IN=@Amnt
If @Cnt=3
BEGIN
Select @Date3_CH=@Date, @Amt3_IN=@Amnt
Select @tmpId, @Date1_CH, @Amt1_IN, @Date2_CH, @Amt2_IN, @Date3_CH, @Amt3_IN
--Insert Table_2(Table_2_ID, Date1_CH, Amnt1_IN, Date2_CH, Amnt2_IN, Date3_CH, Amnt3_IN)
--values(@tmpId, @Date1_CH, @Amt1_IN, @Date2_CH, @Amt2_IN, @Date3_CH, @Amt3_IN)
END
Fetch next from Tbl1 into @Id, @Date, @Amnt
END
Close Tbl1
Deallocate Tbl1 The lines marked in GREEN are commented, so as to show how the records would be built. Instead you would need to uncomment that and comment the line in BLUE, so that it inserts the records into Table_2
NOTE : This has been written assuming that every TABLE_1's ID would have 3 records each. So this won't work for anything more or less than 3 rows, for which the code needs to be altered.
Use this to execute the procexecute InsertTable2 Hope that helps.
Cheers!
_________________________
- Vijay G
Strive for Perfection
|
|
Reply By:
|
mcinar
|
Reply Date:
|
10/8/2004 10:43:53 PM
|
Happygv,
The number of rows might change.
It was just a small sample.
Your recommended link was very helpful.
It didn't exactly do what I had on my mind, but I was still able to make it work and I am using this stored procedure in Crystal Report. That's why I needed to rotate the table.
Here is the code;
=========================================================
select Company_Name_VC, market_company_ID, Symbol_Code_VC, Item_Name_VC, Item_ID,
Market_Name_VC, CIK_Code_VC, Industry_Title_VC,
Date_1 = (select Date_CH from #tReport
where company_name_vc = a.company_name_vc
and item_name_vc = a.item_name_vc and
year(cast(date_ch as datetime)) = '1999'),
Date_2 = (select Date_CH from #tReport
where company_name_vc = a.company_name_vc
and item_name_vc = a.item_name_vc and
year(cast(date_ch as datetime)) = '2000'),
Date_3 = (select Date_CH from #tReport
where company_name_vc = a.company_name_vc
and item_name_vc = a.item_name_vc and
year(cast(date_ch as datetime)) = '2001'),
Date_4 = (select Date_CH from #tReport
where company_name_vc = a.company_name_vc
and item_name_vc = a.item_name_vc and
year(cast(date_ch as datetime)) = '2002'),
Date_5 = (select Date_CH from #tReport
where company_name_vc = a.company_name_vc
and item_name_vc = a.item_name_vc and
year(cast(date_ch as datetime)) = '2003'),
Date_6 = (select Date_CH from #tReport
where company_name_vc = a.company_name_vc
and item_name_vc = a.item_name_vc and
year(cast(date_ch as datetime)) = '2004'),
Amt_1 = (select Amount_IN from #tReport
where company_name_vc = a.company_name_vc
and item_name_vc = a.item_name_vc and
year(cast(date_ch as datetime)) = '1999'),
Amt_2 = (select Amount_IN from #tReport
where company_name_vc = a.company_name_vc
and item_name_vc = a.item_name_vc and
year(cast(date_ch as datetime)) = '2000'),
Amt_3 = (select Amount_IN from #tReport
where company_name_vc = a.company_name_vc
and item_name_vc = a.item_name_vc and
year(cast(date_ch as datetime)) = '2001'),
Amt_4 = (select Amount_IN from #tReport
where company_name_vc = a.company_name_vc
and item_name_vc = a.item_name_vc and
year(cast(date_ch as datetime)) = '2002'),
Amt_5 = (select Amount_IN from #tReport
where company_name_vc = a.company_name_vc
and item_name_vc = a.item_name_vc and
year(cast(date_ch as datetime)) = '2003'),
Amt_6 = (select Amount_IN from #tReport
where company_name_vc = a.company_name_vc
and item_name_vc = a.item_name_vc and
year(cast(date_ch as datetime)) = '2004')
from #tReport as A
group by company_name_vc, market_company_id,
Symbol_Code_VC, item_name_vc, Item_ID, Market_Name_VC,
CIK_Code_VC, Industry_Title_VC
order by Item_ID
=================================================
Thank you.
MCinar
|
|
Reply By:
|
happygv
|
Reply Date:
|
10/8/2004 10:50:09 PM
|
Hmm... I thought so.
So has it been solved now?
Cheers!
_________________________
- Vijay G
Strive for Perfection
|
|
Reply By:
|
mcinar
|
Reply Date:
|
10/8/2004 10:53:27 PM
|
Thank you, Happygv.
I will test your stored procedure.
You have been very helpful.
MCinar
|
|
Reply By:
|
mcinar
|
Reply Date:
|
10/8/2004 11:01:29 PM
|
Happgv,
It is solved.
Thanks for your help.
Mcinar
|
