Hi,
My query,
SELECT REGION_ID,
LTRIM(MAX(SYS_CONNECT_BY_PATH(RRM, ';'))
KEEP(DENSE_RANK LAST ORDER BY CURR),
';') AS Manager
FROM (SELECT FM.REGION_ID REGION_ID,
(LAST_NAME || ', ' || SUR_NAME) Manager,
ROW_NUMBER() OVER(PARTITION BY FM.REGION_ID ORDER BY (LAST_NAME || ', ' || SUR_NAME)) AS CURR,
ROW_NUMBER() OVER(PARTITION BY FM.REGION_ID ORDER BY (LAST_NAME || ', ' || SUR_NAME)) - 1 AS PREV
FROM USER_DETAIL UD,
SERVICE_MASTER SM,
FUNCTION_MASTER FM
WHERE UD.USER_ID = SM.USER_ID
AND UD.ROLE_ID = 3
AND UD.REGION_ID = FM.REGION_ID)
GROUP BY REGION_ID
CONNECT BY PREV = PRIOR CURR
AND REGION_ID = PRIOR REGION_ID
START WITH CURR = 1
Result of the whole query:
--------------------------
REGION_ID Manager
-----------------------------------------------
SR1 James,Joan;Drucker, Peter;Drucker, Peter
SR3 Jones, Simon
Result of the Inner Query (Inline View) i.e. Query in the FROM Clause
REGION_ID Manager CURR PREV
----------------------------------------------------------------------------------------
SR1 James, Joan 1 0
SR1 Drucker, Peter 2 1
SR1 Drucker, Peter 3 2
SR3 Jones, Simon 1 0
Now what I need is that in Inner Query, the two rows which have same Manager in same region(i.e.Drucker, Peter in same region SR1) should be shown as one and hence when passed to the main query will show me result as
REGION_ID Manager
-----------------------------------------------
SR1 James,Joan;Drucker, Peter --> Drucker, Peter only once as compared
to twice in whole query result.
SR3 Jones, Simon
I need this modification in my above query and not in the new query..
I request u all to help solve this as i tried a lot but in vain..
thx.....