Here are three methods of solving this in Oracle. The first is an Analytic SQL solution that only has to do one full table scan (assuming no indexes). The second is Nick Chammas's CTE solution converted to Oracle syntax and the third is a solution using a PL/SQL cursor FOR loop inside a Pipelined function. The solutions all produce the results expected. For these few rows the analytic solution performs best followed by the recursive CTE. Which is easiest to work with will be in the eye of the beholder.
Analytic Solution
SELECT Project, Item, Method, Min("Start") "Start", Max("End") "End" FROM (
SELECT Project, Item, Method, "Start", "End"
, MAX(Change) OVER (PARTITION BY Project, Item ORDER BY "Start", "End") ChangeGroup
FROM (
SELECT Project, Item, Method, "Start", "End"
, CASE WHEN Method =
LAG(Method) OVER (PARTITION BY Project, Item ORDER BY "Start", "End") THEN NULL
ELSE Row_Number() OVER (ORDER BY Project, Item, "Start", "End")
END Change
FROM T1
)
)
GROUP BY Project, Item, Method, ChangeGroup
ORDER BY 1, 2, 4, 5;
CTE Solution
WITH connected_ranges (Project, Method, Item, "Start", "End") AS (
SELECT right_range.project, right_range.method, right_range.item, right_range."Start"
, right_range."End"
FROM T1 left_range
RIGHT OUTER JOIN T1 right_range
ON right_range.project = left_range.project
AND right_range.item = left_range.item
AND right_range.method = left_range.method
AND right_range."Start" = left_range."End"
WHERE left_range.project IS NULL
UNION ALL
SELECT right_range.project, right_range.method, right_range.item, left_range."Start"
, right_range."End"
FROM connected_ranges left_range
INNER JOIN T1 right_range
ON right_range.project = left_range.project
AND right_range.item = left_range.item
AND right_range.method = left_range.method
AND right_range."Start" = left_range."End"
)
SELECT project, item, Method, "Start", MAX("End") AS "End"
FROM connected_ranges
GROUP BY project, method, item, "Start"
ORDER BY 1, 2, 4, 5;
PL/SQL Cursor FOR Loop Solution
CREATE OR REPLACE
CREATE OR REPLACE FUNCTION PipeResult Return
BEGIN
For vLoop In (
SELECT Project, Item, Method, "Start", "End"
, LAG(Method) OVER (PARTITION BY Project, Item ORDER BY "Start", "End") LagMethod
) Loop
Pipe Row
End Loop;
END;
/
CREATE OR REPLACE PACKAGE Example AUTHID DEFINER AS
Type tRow Is Record (
Project Varchar2(3)
, Item Varchar2(7)
, Method Varchar2(2)
, "Start" Number(4)
, "End" Number(4)
);
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;
END Example;
/
CREATE OR REPLACE PACKAGE BODY Example AS
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
vSavedRow tRow;
Begin
For vRow IN (
SELECT Project, Item, Method, "Start", "End"
, LEAD(Method) OVER (PARTITION BY Project, Item ORDER BY "Start", "End") LeadMethod
FROM T1
ORDER BY Project, Item, "Start", "End"
)
Loop
If (vSavedRow.Method IS NULL) Then
vSavedRow.Project := vRow.Project;
vSavedRow.Item := vRow.Item;
vSavedRow.Method := vRow.Method;
vSavedRow."Start" := vRow."Start";
End If;
vSavedRow."End" := vRow."End";
If (vRow.Method <> vRow.LeadMethod OR vRow.LeadMethod IS NULL) Then
Pipe Row(vSavedRow);
vSavedRow.Method := NULL;
End If;
End Loop;
End;
END Example;
/
SELECT * FROM TABLE(Example.PipelineResult);
Demonstration Environment:
DROP TABLE T1;
CREATE TABLE T1 AS (
SELECT 'ABC' Project, 'widget1' Item, 'XY' Method, 1000 "Start",
1033 "End"FROM dual);
INSERT INTO T1 VALUES ('ABC','widget1','XY',1033,1062);
INSERT INTO T1 VALUES ('ABC','widget1','XY',1062,1112);
INSERT INTO T1 VALUES ('ABC','widget1','XY',1112,1163);
INSERT INTO T1 VALUES ('ABC','widget1','WW',1163,1223);
INSERT INTO T1 VALUES ('ABC','widget1','WW',1223,1288);
INSERT INTO T1 VALUES ('ABC','widget1','WW',1288,1334);
INSERT INTO T1 VALUES ('ABC','widget1','XY',1334,1383);
INSERT INTO T1 VALUES ('ABC','widget1','XY',1383,1425);
COMMIT;
min(start),max(end)
for eachproject,item,method
group. Where in your test data are the gaps you are talking about and how are they removed? can you clarify that? – miracle173 Sep 20 '12 at 5:16