Home » SQL & PL/SQL » SQL & PL/SQL » Sql query
Sql query [message #231581] Tue, 17 April 2007 10:33 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Hi All,
Required sql to get the report.
Here is the source table


-------------------------------------------------------
   Product    |	   Business line     |	   coverage   |
------------------------------------------------------
PRODLINE_001  |	BUSINESS_LINE_2066   |	COV_LINE_1044 |
PRODLINE_001  |	BUSINESS_LINE_2042   |	              |
PRODLINE_001  |	BUSINESS_LINE_2062   |	              | 
PRODLINE_002  |	BUSINESS_LINE_2011   |	              | 
PRODLINE_003  |		             |                |   
PRODLINE_004  |	BUSINESS_LINE_2014   |	COV_LINE_1044 |
-------------------------------------------------------



output required :

-------------------------------------------------------
   Product    |	   Business line     |	   coverage   |
------------------------------------------------------
PRODLINE_001  |	BUSINESS_LINE_2066   |	COV_LINE_1044 |
PRODLINE_001  |	BUSINESS_LINE_2066   |	              |
PRODLINE_001  |                      |	              |
PRODLINE_001  |	BUSINESS_LINE_2042   |	              |
PRODLINE_001  |                      |	              |
PRODLINE_001  |	BUSINESS_LINE_2062   |	              | 
PRODLINE_001  |                      |	              |
PRODLINE_002  |	BUSINESS_LINE_2011   |	              | 
PRODLINE_002  |                      |	              |
PRODLINE_003  |		             |                |   
PRODLINE_004  |	BUSINESS_LINE_2014   |	COV_LINE_1044 |
PRODLINE_004  |	BUSINESS_LINE_2014   |	              |
PRODLINE_004  |	                     |	              |
-------------------------------------------------------




and also vice versa...

If information is not sufficient let me know pls...

Regards,
Naveen

[Updated on: Tue, 17 April 2007 10:33]

Report message to a moderator

Re: Sql query [message #231583 is a reply to message #231581] Tue, 17 April 2007 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, create table and insert statements are missing.

Regards
Michel
Re: Sql query [message #231587 is a reply to message #231583] Tue, 17 April 2007 11:05 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

here you go....

create table sample_tab
( Product       varchar2(30),
  Business_line  varchar2(30),
  coverage       varchar2(30));

insert into sample_tab (Product,Business_line,coverage)values('PRODLINE_001','BUSINESS_LINE_2066','COV_LINE_1044');

insert into sample_tab (Product,Business_line,coverage)values('PRODLINE_001','BUSINESS_LINE_2042',Null);

insert into sample_tab (Product,Business_line,coverage)values('PRODLINE_001','BUSINESS_LINE_2062',Null);

insert into sample_tab (Product,Business_line,coverage)values('PRODLINE_002','BUSINESS_LINE_2011',Null);

insert into sample_tab (Product,Business_line,coverage)values('PRODLINE_003',null,null);

insert into sample_tab (Product,Business_line,coverage)values('PRODLINE_004','BUSINESS_LINE_2014','COV_LINE_1044');







create table sample_tab_a
( Product       varchar2(30),
  Business_line  varchar2(30),
  coverage       varchar2(30));

insert into sample_tab_a (Product,Business_line,coverage)values('PRODLINE_001','BUSINESS_LINE_2066','COV_LINE_1044');
insert into sample_tab_a (Product,Business_line,coverage)values('PRODLINE_001','BUSINESS_LINE_2066',null);
insert into sample_tab_a (Product,Business_line,coverage)values('PRODLINE_001',null,null);
insert into sample_tab_a (Product,Business_line,coverage)values('PRODLINE_001','BUSINESS_LINE_2042',Null);
insert into sample_tab_a (Product,Business_line,coverage)values('PRODLINE_001',null,Null);
insert into sample_tab_a (Product,Business_line,coverage)values('PRODLINE_001','BUSINESS_LINE_2062',Null);
insert into sample_tab_a (Product,Business_line,coverage)values('PRODLINE_001',null,Null);
insert into sample_tab_a (Product,Business_line,coverage)values('PRODLINE_002','BUSINESS_LINE_2011',Null);
insert into sample_tab_a (Product,Business_line,coverage)values('PRODLINE_002',null,Null);
insert into sample_tab_a (Product,Business_line,coverage)values('PRODLINE_003',null,null);
insert into sample_tab_a (Product,Business_line,coverage)values('PRODLINE_004','BUSINESS_LINE_2014','COV_LINE_1044');
insert into sample_tab_a (Product,Business_line,coverage)values('PRODLINE_004','BUSINESS_LINE_2014',null);
insert into sample_tab_a (Product,Business_line,coverage)values('PRODLINE_004',null,null);




Naveen
Re: Sql query [message #231593 is a reply to message #231587] Tue, 17 April 2007 12:05 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    lines as (select rownum line from dual connect by level <= 3 ),
  3    data as (
  4      select product, business_line, coverage,
  5             1 + decode(business_line,null,0,1) + decode(coverage,null,0,1) cnt
  6      from sample_tab
  7    )
  8  select product,
  9         case when line <= cnt-1 then business_line end business_line,
 10         case when line <= cnt-2 then coverage end coverage
 11  from data, lines
 12  where line <= cnt
 13  order by 1, 2, 3
 14  /
PRODUCT                        BUSINESS_LINE                  COVERAGE
------------------------------ ------------------------------ ------------------------------
PRODLINE_001                   BUSINESS_LINE_2042
PRODLINE_001                   BUSINESS_LINE_2062
PRODLINE_001                   BUSINESS_LINE_2066             COV_LINE_1044
PRODLINE_001                   BUSINESS_LINE_2066
PRODLINE_001
PRODLINE_001
PRODLINE_001
PRODLINE_002                   BUSINESS_LINE_2011
PRODLINE_002
PRODLINE_003
PRODLINE_004                   BUSINESS_LINE_2014             COV_LINE_1044
PRODLINE_004                   BUSINESS_LINE_2014
PRODLINE_004

13 rows selected.

SQL> with 
  2    data as (
  3      select product, business_line, coverage,
  4             case 
  5             when ( lag(product) over (order by product, business_line, coverage) = product
  6                  and lag(business_line) over (order by product, business_line, coverage) = business_line
  7                  and lag(coverage) over (order by product, business_line, coverage) != coverage
  8                  and coverage is not null )
  9               or ( lag(product) over (order by product, business_line, coverage) = product
 10                  and lag(business_line) over (order by product, business_line, coverage) != business_line
 11                  and business_line is not null )
 12               or lag(product) over (order by product, business_line, coverage) != product
 13               or lag(product) over (order by product, business_line, coverage) is null
 14             then 1 end flag
 15      from sample_tab_a
 16    )
 17  select product, business_line, coverage
 18  from data
 19  where flag = 1
 20  order by 1, 2, 3
 21  /
PRODUCT                        BUSINESS_LINE                  COVERAGE
------------------------------ ------------------------------ ------------------------------
PRODLINE_001                   BUSINESS_LINE_2042
PRODLINE_001                   BUSINESS_LINE_2062
PRODLINE_001                   BUSINESS_LINE_2066             COV_LINE_1044
PRODLINE_002                   BUSINESS_LINE_2011
PRODLINE_003
PRODLINE_004                   BUSINESS_LINE_2014             COV_LINE_1044

6 rows selected.

Regards
Michel
Previous Topic: UTL_FILE format
Next Topic: Query problems
Goto Forum:
  


Current Time: Thu Dec 08 16:12:13 CST 2016

Total time taken to generate the page: 0.11773 seconds