Home » SQL & PL/SQL » SQL & PL/SQL » tough query,help me
tough query,help me [message #187322] Sat, 12 August 2006 01:01 Go to next message
born2acheive
Messages: 4
Registered: August 2006
Junior Member
hi

i have datas like this

id from to
1 500 700
1 600 900
1 650 950
1 1000 1100

i need ans like this:

1 1000,1100,500,950

becos 600 lies between 500 to 700 and 650 lies between to 600 to 900 but 1000 in not lising so i neet output like that,please give me query to do that please
Re: tough query,help me [message #187386 is a reply to message #187322] Sun, 13 August 2006 03:38 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
why dont you show the code which you have?which might help us to understand your query?
and try searching for pivot in forum
Re: tough query,help me [message #187850 is a reply to message #187386] Wed, 16 August 2006 00:32 Go to previous messageGo to next message
born2acheive
Messages: 4
Registered: August 2006
Junior Member
hi amul, i din't have code, i am requesting query for my need from u,please give me
Re: tough query,help me [message #188008 is a reply to message #187850] Wed, 16 August 2006 13:25 Go to previous messageGo to next message
shivaram9
Messages: 35
Registered: August 2006
Member
Hi,

I am new to pl/sql programming but wrote this anonymus block, might be this will help you little bit, as per your data i created a temp table.

create table temp(a varchar2(100),b varchar2(100),c varchar2(100));

INSERT INTO temp (a,b,c) VALUES (1,500,700);
INSERT INTO temp (a,b,c) VALUES (1,600,900);
INSERT INTO temp (a,b,c) VALUES (1,650,950);
INSERT INTO temp (a,b,c) VALUES (1,1000,1100);

DECLARE
v_cntr NUMBER:= 0;
v_flg BOOLEAN := FALSE;
v_flg_2 BOOLEAN := FALSE;
v_flg_3 BOOLEAN := FALSE;
CURSOR c_temp_1 IS
SELECT b,c
FROM temp ORDER BY to_number(b),to_number(c) ASC;

CURSOR c_temp_2 IS
SELECT b t,c e
FROM temp ORDER BY to_number(b),to_number(c) ASC;

TYPE T_temp IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
V_temp1 T_temp;
V_temp2 T_temp;

v_cnt NUMBER;
q NUMBER;
z NUMBER;
c NUMBER;
e NUMBER;
v_temp VARCHAR2(100);
cnt NUMBER;
BEGIN

SELECT COUNT(1)
INTO v_cnt
FROM temp;

FOR i IN c_temp_1
LOOP
v_cntr := v_cntr+1;
FOR j IN c_temp_2
LOOP
IF (j.t > i.b) AND (j.t < i.c)
THEN
IF (v_temp1.COUNT > 0)
THEN
FOR q IN v_temp1.FIRST .. v_temp1.LAST
LOOP
IF (i.b) = v_temp1(q)
THEN
v_flg := TRUE;
EXIT;
END IF;
END LOOP;
END IF;
IF NOT v_flg
THEN
c := nvl(V_temp1.last, 0) + 1;
V_temp1(c) := i.b;
END IF;
v_flg := FALSE;
ELSIF (v_cntr = v_cnt)
THEN
IF (v_temp2.COUNT >0)
THEN
FOR z IN v_temp2.FIRST .. v_temp2.LAST
LOOP
IF (i.b) = v_temp2(z)
THEN
v_flg_2 := TRUE;
EXIT;
END IF;
END LOOP;
END IF;

IF NOT v_flg_2
THEN
e := nvl(V_temp2.last, 0) + 1;
V_temp2(e) := i.b;
e := nvl(V_temp2.last, 0) + 1;
V_temp2(e) := i.c;
END IF;
v_flg_2 := FALSE;
ELSIF (v_cntr = v_cnt-1)
THEN
IF (v_temp2.COUNT >0)
THEN
FOR z IN v_temp2.FIRST .. v_temp2.LAST
LOOP
IF (i.c) = v_temp2(z)
THEN
v_flg_3 := TRUE;
EXIT;
END IF;
END LOOP;
END IF;

IF NOT v_flg_3
THEN
e := nvl(V_temp2.last, 0) + 1;
V_temp2(e) := i.c;
END IF;
END IF;
END LOOP;

END LOOP;

cnt := 1;
FOR e IN V_temp2.FIRST .. V_temp2.LAST
LOOP
IF cnt = 1
THEN
v_temp := v_temp1(1)||' ,'||v_temp2(e);
ELSE
v_temp := v_temp||' ,'||v_temp2(e);
END IF;
cnt := cnt+1;

END LOOP;

dbms_output.put_line('----v_temp-----'||v_temp);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('---sqlerrm----'||SQLERRM);
END;


Might be some one could write a easy code.

Let me know if you have any questions.

Thanks,
shivaram.
Re: tough query,help me [message #188090 is a reply to message #187322] Thu, 17 August 2006 01:50 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The tricky bit is grouping the rows into contiguous ranges - getting them all on one line is simple a case of using STRAGG or one of the other solutions that crop up here on a daily basis.

Here's my stab at getting the data grouped into contiguous ranges:

create table contig_Test (id  number, val_from  number, val_to number);

insert into contig_test values (1, 500 ,700);
insert into contig_test values (1, 600 ,900);
insert into contig_test values (1, 650 ,950);
insert into contig_test values (1, 1000, 1100);


select distinct
       id
      ,nvl(val_from,lag(val_from) over (order by rnum)) val_from
      ,nvl(val_to ,lead(val_to)   over (order by rnum)) val_to
from (select id
            ,decode(prev_Date,null,val_from,null) val_from
            ,decode(next_date,null,val_to  ,null) val_to
            ,rnum
      from (select id
                  ,val_from
                  ,val_to
                  ,case when lead(val_from) over (order by val_from) > max(val_to) over (order by val_from) then null
                        else lead(val_from) over (order by val_from) 
                        end  next_date
                  ,case when lag(val_to) over (order by val_from) < max(val_from) over (order by val_from) then null
                        else lag(val_to) over (order by val_from)
                        end  prev_Date
                 ,row_number() over (order by val_from) rnum
            from   contig_test 
            order by val_from)
      where next_date is null or prev_date is null);

ID    VAL_FROM      VAL_TO
---------------------------
1     500           950
1     1000          1100
Previous Topic: Pivot Select query for a Report
Next Topic: Splitting total records returned
Goto Forum:
  


Current Time: Tue Dec 06 16:20:38 CST 2016

Total time taken to generate the page: 0.14302 seconds