Making 3 rows become 1 row in another table

mcinar · October 7th, 2004, 07:27 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.

happygv · October 7th, 2004, 11:00 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

r_ganesh76 · October 8th, 2004, 12:06 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

mcinar · October 8th, 2004, 06:19 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.

mcinar · October 8th, 2004, 06:21 AM

Hi Ganesh,

I have a primary key and I need to make it happen in the stored procedure.

Thanks for your help.

happygv · October 8th, 2004, 10:09 PM

Are you sure for every Table_1_ID in Table_1 there would be three rows ALWAYS?

_________________________
- Vijay G
Strive for Perfection

happygv · October 8th, 2004, 10:27 PM

Hi mcinar,

If so(every ID would have 2 rows each for sure), this should be what you were looking for.

Code:

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 proc

Code:

execute InsertTable2

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

mcinar · October 8th, 2004, 10:43 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

happygv · October 8th, 2004, 10:50 PM

Hmm... I thought so.

So has it been solved now?

Cheers!

_________________________
- Vijay G
Strive for Perfection

mcinar · October 8th, 2004, 10:53 PM

Thank you, Happygv.
I will test your stored procedure.
You have been very helpful.

MCinar