Home » SQL & PL/SQL » SQL & PL/SQL » Columns to Rows and De Duping
Columns to Rows and De Duping [message #185355] Tue, 01 August 2006 05:35 Go to next message
kw57
Messages: 3
Registered: August 2006
Junior Member
I have 2 tables (TABLE 1 and TABLE 3) containing many columns which I need to produce a report from in SQL. TABLE 1 contains 3million + rows and 30 columns.

I need to produce a report in the format shown below that eliminates the rows containing duplicate Descriptions and shows the Budget and Actual values. If the Budget and Actual values are both NULL then this must not be displayed. If the Amount has a value of 0 or greater then the row must be displayed.

Note: In the T1 LT column 'AA' represents a Budget value and 'BA' represents an Actual value n the 'Amount' column.

Note: The T1 ID column does not have unique entries and is used to relate to the Description in TABLE 3.

I have tried many ways to get the required result but always get anomalies that I cannot eliminate. How do I solve this problem?

All assistance will be gratefully received.


TABLE 1

T1 ID T1 LT Amount
1001 AA 3956
1001 BA <null>
1002 AA <null>
1003 AA 2145
1004 AA 0
1004 BA <null>
1005 AA <null>
1006 AA <null>
1007 AA <null>
1008 AA <null>
1008 BA 1854
1009 AA <null>
1009 BA <null>
1010 AA 5868
1010 BA 6500
1011 AA 2145
1011 BA 2000
1012 AA 5987
1012 BA 6000
1013 AA 6242
1013 BA 5000
1014 AA 8221
1014 BA 8000
1015 AA 1365
1015 BA 1000
1016 BA <null>
1017 AA <null>
1017 BA 1400
1018 BA <null>
1019 BA <null>
1020 BA <null>
1021 BA <null>
1022 AA <null>
1022 BA 6750
1023 BA 1000
1024 AA <null>
1024 BA 24000
1025 AA <null>
1025 BA <null>
1026 AA <null>
1026 BA <null>
1027 AA <null>
1027 BA <null>




TABLE 3

T3 ID Description
1001 Car Allowance
1002 Expense Claim
1003 Local Courier
1004 Local Deliveries
1005 Market Research
1006 Office Supplies
1007 Promotional
1008 Restaurant
1009 Telephone
1010 Expense Claim
1011 Local Courier
1012 Market Research
1013 Office Supplies
1014 Promotional
1015 Telephone
1016 Expense Claim
1017 Garden
1018 Local Courier
1019 Market Research
1020 Office Supplies
1021 Promotional
1022 Special
1023 Telephone - Mobile
1024 Travel
1025 Travel - International
1026 Work - External
1027 Work - Internal



REQUIRED REPORT (Sorted by Description)

ID Description Budget Actual
1001 Car Allowance 0 3956
1010 Expense Claim 6500 5868
1017 Garden 1400 0
1011 Local Courier 2000 2145
1004 Local Deliveries 0 0
1012 Market Research 6000 5987
1013 Office Supplies 5000 6242
1014 Promotional 8000 8221
1008 Restaurant 1854 0
1022 Special 6750 0
1015 Telephone 1000 1365
1024 Travel 24000 0

Re: Columns to Rows and De Duping [message #185361 is a reply to message #185355] Tue, 01 August 2006 06:12 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

There Can be many ways to do this ..

One of the Ways ...

Select * from (
select t3.T3_ID, t3.Description 
       (Select NVL(t1.Amount,0)
        From TABLE_1 t1
        where t1.T1_ID =t3.T3_ID
        and t1.T1_LT='BA') Budget_Amt ,
       (Select NVL(t2.Amount,0)
        From TABLE_1 t2
        where t2.T1_ID =t3.T3_ID
        and t2.T1_LT='AA') Actual_Amt 
FROM TABLE_3) T
where Budget_Amt <>0
AND   Actual_Amt <>0
ORDER BY Description;


Thumbs Up
Rajuvan.

[Updated on: Tue, 01 August 2006 08:23]

Report message to a moderator

Re: Columns to Rows and De Duping [message #185379 is a reply to message #185355] Tue, 01 August 2006 08:11 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Another way using lead function...

select t1id,des,nvl(amount_2,0)Budget ,nvl(amount,0)Actual 
from ( select t1id,des,amount,amount_2 
	     from (select t1id,t1lt,des,amount,ROW_NUMBER() OVER ( PARTITION by t1id order by t1id) rn,
		 	  		  lead(t1id) over (partition by t1id order by t1id)   t1id_2,
					  lead(t1lt) over (partition by t1id order by t1id)  t1lt_2,
					  lead(des) over (partition by t1id order by t1id)  des_2,
					  lead(amount) over (partition by t1id order by t1id)  amount_2
			     from table3,table1
				 where t1id= t3id)
		where t1id = t1id_2)
where amount is not null 
  or amount_2 is not null




Naveen
Re: Columns to Rows and De Duping [message #185385 is a reply to message #185355] Tue, 01 August 2006 08:36 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Or It can be as simple as ...

Select T3.T3_ID ,T3.Description
FROM 
TABLE_3 T3,
(Select T1_ID , 
       MAX(DECODE(T1_LT,'BA',NVL(Amount,0))) Budget_Amt,
       MAX(DECODE(T1_LT,'AA',NVL(Amount,0))) Actual_Amt,
From TABLE_1 
Group by T1_ID) T1
where T3.T3_ID = T1.T1_ID
and T1.Budget_Amt <>0
and T1.Actual_Amt <>0


Rajuvan.




Re: Columns to Rows and De Duping [message #185387 is a reply to message #185355] Tue, 01 August 2006 08:43 Go to previous message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Rajuvan,
If i execute your script it only returns 6 rows, but it should return 12 rows...

Here is the complete script which returns 12 rows...


SQL> ed
Wrote file afiedt.buf

  1  create table table1
  2  (t1id  number,
  3   t1lt varchar2(5),
  4*  Amount  number)
SQL> /

Table created.

SQL> ed
Wrote file afiedt.buf

  1  begin
  2  insert into table1 values(1001 ,'AA', 3956);
  3  insert into table1 values(1001 ,'BA',null);
  4  insert into table1 values(1002 ,'AA',null);
  5  insert into table1 values(1003 ,'AA', 2145);
  6  insert into table1 values(1004 ,'AA', 0);
  7  insert into table1 values(1004 ,'BA',null);
  8  insert into table1 values(1005 ,'AA',null);
  9  insert into table1 values(1006 ,'AA',null);
 10  insert into table1 values(1007 ,'AA',null);
 11  insert into table1 values(1008 ,'AA',null);
 12  insert into table1 values(1008 ,'BA', 1854);
 13  insert into table1 values(1009 ,'AA',null);
 14  insert into table1 values(1009 ,'BA',null);
 15  insert into table1 values(1010 ,'AA', 5868);
 16  insert into table1 values(1010 ,'BA', 6500);
 17  insert into table1 values(1011 ,'AA', 2145);
 18  insert into table1 values(1011 ,'BA', 2000);
 19  insert into table1 values(1012 ,'AA', 5987);
 20  insert into table1 values(1012 ,'BA', 6000);
 21  insert into table1 values(1013 ,'AA', 6242);
 22  insert into table1 values(1013 ,'BA', 5000);
 23  insert into table1 values(1014 ,'AA', 8221);
 24  insert into table1 values(1014 ,'BA', 8000);
 25  insert into table1 values(1015 ,'AA', 1365);
 26  insert into table1 values(1015 ,'BA', 1000);
 27  insert into table1 values(1016 ,'BA',null);
 28  insert into table1 values(1017 ,'AA',null);
 29  insert into table1 values(1017 ,'BA', 1400);
 30  insert into table1 values(1018 ,'BA',null);
 31  insert into table1 values(1019 ,'BA',null);
 32  insert into table1 values(1020 ,'BA',null);
 33  insert into table1 values(1021 ,'BA',null);
 34  insert into table1 values(1022 ,'AA',null);
 35  insert into table1 values(1022 ,'BA',6750);
 36  insert into table1 values(1023 ,'BA',1000);
 37  insert into table1 values(1024 ,'AA',null);
 38  insert into table1 values(1024 ,'BA',24000);
 39  insert into table1 values(1025 ,'AA',null);
 40  insert into table1 values(1025 ,'BA',null);
 41  insert into table1 values(1026 ,'AA',null);
 42  insert into table1 values(1026 ,'BA',null);
 43  insert into table1 values(1027 ,'AA',null);
 44  insert into table1 values(1027 ,'BA',null);
 45* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.


SQL> select * from table1;

      T1ID T1LT      AMOUNT
---------- ----- ----------
      1001 AA          3956
      1001 BA
      1002 AA
      1003 AA          2145
      1004 AA             0
      1004 BA
      1005 AA
      1006 AA
      1007 AA
      1008 AA
      1008 BA          1854

      T1ID T1LT      AMOUNT
---------- ----- ----------
      1009 AA
      1009 BA
      1010 AA          5868
      1010 BA          6500
      1011 AA          2145
      1011 BA          2000
      1012 AA          5987
      1012 BA          6000
      1013 AA          6242
      1013 BA          5000
      1014 AA          8221

      T1ID T1LT      AMOUNT
---------- ----- ----------
      1014 BA          8000
      1015 AA          1365
      1015 BA          1000
      1016 BA
      1017 AA
      1017 BA          1400
      1018 BA
      1019 BA
      1020 BA
      1021 BA
      1022 AA

      T1ID T1LT      AMOUNT
---------- ----- ----------
      1022 BA          6750
      1023 BA          1000
      1024 AA
      1024 BA         24000
      1025 AA
      1025 BA
      1026 AA
      1026 BA
      1027 AA
      1027 BA

43 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  create table table3
  2  (t3id  number,
  3*  Des   varchar2(30))
SQL> /

Table created.

SQL> ed
Wrote file afiedt.buf

  1  begin
  2  insert into table3 values(1001  ,'Car Allowance');
  3  insert into table3 values(1002  ,'Expense Claim');
  4  insert into table3 values(1003  ,'Local Courier');
  5  insert into table3 values(1004  ,'Local Deliveries');
  6  insert into table3 values(1005  ,'Market Research');
  7  insert into table3 values(1006  ,'Office Supplies');
  8  insert into table3 values(1007  ,'Promotional');
  9  insert into table3 values(1008  ,'Restaurant');
 10  insert into table3 values(1009  ,'Telephone');
 11  insert into table3 values(1010  ,'Expense Claim');
 12  insert into table3 values(1011  ,'Local Courier');
 13  insert into table3 values(1012  ,'Market Research');
 14  insert into table3 values(1013  ,'Office Supplies');
 15  insert into table3 values(1014  ,'Promotional');
 16  insert into table3 values(1015  ,'Telephone');
 17  insert into table3 values(1016  ,'Expense Claim');
 18  insert into table3 values(1017  ,'Garden');
 19  insert into table3 values(1018  ,'Local Courier');
 20  insert into table3 values(1019  ,'Market Research');
 21  insert into table3 values(1020 ,'Office Supplies');
 22  insert into table3 values(1021 ,'Promotional');
 23  insert into table3 values(1022 ,'Special');
 24  insert into table3 values(1023 ,'Telephone - Mobile');
 25  insert into table3 values(1024 ,'Travel');
 26  insert into table3 values(1025 ,'Travel - International');
 27  insert into table3 values(1026 ,'Work - External');
 28  insert into table3 values(1027 ,'Work - Internal');
 29* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.


SQL> select * from table3;

      T3ID DES
---------- ------------------------------
      1001 Car Allowance
      1002 Expense Claim
      1003 Local Courier
      1004 Local Deliveries
      1005 Market Research
      1006 Office Supplies
      1007 Promotional
      1008 Restaurant
      1009 Telephone
      1010 Expense Claim
      1011 Local Courier

      T3ID DES
---------- ------------------------------
      1012 Market Research
      1013 Office Supplies
      1014 Promotional
      1015 Telephone
      1016 Expense Claim
      1017 Garden
      1018 Local Courier
      1019 Market Research
      1020 Office Supplies
      1021 Promotional
      1022 Special

      T3ID DES
---------- ------------------------------
      1023 Telephone - Mobile
      1024 Travel
      1025 Travel - International
      1026 Work - External
      1027 Work - Internal

27 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  select t1id id,des,nvl(amount_2,0)Budget ,nvl(amount,0)Actual
  2  from ( select t1id,des,amount,amount_2
  3          from (select t1id,t1lt,des,amount,ROW_NUMBER() OVER ( PARTITION by t1id order by t1id) rn
  4                                       lead(t1id) over (partition by t1id order by t1id)   t1id_2,
  5                                       lead(t1lt) over (partition by t1id order by t1id)  t1lt_2,
  6                                       lead(des) over (partition by t1id order by t1id)  des_2,
  7                                       lead(amount) over (partition by t1id order by t1id)  amount_2
  8                          from table3,table1
  9                              where t1id= t3id)
 10             where t1id = t1id_2)
 11  where amount is not null
 12    or amount_2 is not null
 13* order by des
SQL> /

        ID DES                                BUDGET     ACTUAL
---------- ------------------------------ ---------- ----------
      1001 Car Allowance                           0       3956
      1010 Expense Claim                        6500       5868
      1017 Garden                               1400          0
      1011 Local Courier                        2000       2145
      1004 Local Deliveries                        0          0
      1012 Market Research                      6000       5987
      1013 Office Supplies                      5000       6242
      1014 Promotional                          8000       8221
      1008 Restaurant                           1854          0
      1022 Special                              6750          0
      1015 Telephone                            1000       1365

        ID DES                                BUDGET     ACTUAL
---------- ------------------------------ ---------- ----------
      1024 Travel                              24000          0

12 rows selected.

SQL> 


Let me know if i am wrong...

Naveen
Previous Topic: Interface to an external system
Next Topic: update one table with the values from second table
Goto Forum:
  


Current Time: Fri Dec 02 12:44:33 CST 2016

Total time taken to generate the page: 0.09263 seconds