Hi Gurus,
I have following input
WITH
mytable (RANS_ID,
T_ID,
ACT_NAME,
ROLE_NAME)
AS
(SELECT '1,354' AS RANS_ID,
'1,005' AS T_ID,
'bcomfey,rbllfn' AS ACT_NAME,
NULL AS ROLE_NAME
FROM DUAL
UNION ALL
SELECT '1,354', '1,005', NULL, 'Admin,MAN' FROM DUAL
UNION ALL
SELECT '1,364', '1,005', NULL, 'Admin' FROM DUAL
UNION ALL
SELECT '1,365', '1,005', NULL, 'MAN' FROM DUAL
UNION ALL
SELECT '1,366', '1,005', NULL, 'Any' FROM DUAL
UNION ALL
SELECT '1,373', '1,005', 'rbllfn', NULL FROM DUAL
UNION ALL
SELECT '1,353', '1,004', 'bcomfey,rbllfn', NULL FROM DUAL
UNION ALL
SELECT '1,353', '1,004', NULL, 'Any' FROM DUAL
UNION ALL
SELECT '1,352', '1,003', 'bcomfey,rbllfn', NULL FROM DUAL
UNION ALL
SELECT '1,352', '1,003', NULL, 'Any' FROM DUAL)
SELECT *
FROM mytable;
Expected output
WITH
mytable (RANS_ID,
T_ID,
ACT_NAME,
ROLE_NAME)
AS
(SELECT '1,352' AS RANS_ID,
'1,003' AS T_ID,
'bcomfey,rbllfn' AS ACT_NAME,
'Any' AS ROLE_NAME
FROM DUAL
UNION ALL
SELECT '1,353', '1,004', 'bcomfey,rbllfn', 'Any' FROM DUAL
UNION ALL
SELECT '1,354', '1,005', 'bcomfey,rbllfn', 'Admin,MAN' FROM DUAL
UNION ALL
SELECT '1,364', '1,005', NULL, 'Admin' FROM DUAL
UNION ALL
SELECT '1,365', '1,005', NULL, 'MAN' FROM DUAL
UNION ALL
SELECT '1,366', '1,005', NULL, 'Any' FROM DUAL
UNION ALL
SELECT '1,373', '1,005', 'rbllfn', NULL FROM DUAL)
SELECT *
FROM mytable; --output
