If possible do it in java.
Maybe this can help,
SELECT EMPID,
MAX(dept1) || ',' || MAX(dept2) || ',' || MAX(dept3) || ',' || MAX(dept4)
FROM
(SELECT EMPID,
CASE ranking
WHEN 1 THEN
EMPDEPT
ELSE
NULL
END dept1,
CASE ranking
WHEN 2 THEN
EMPDEPT
ELSE
NULL
END dept2,
CASE ranking
WHEN 3 THEN
EMPDEPT
ELSE
NULL
END dept3,
CASE ranking
WHEN 4 THEN
EMPDEPT
ELSE
NULL
END dept4
FROM
(SELECT rank() over(
ORDER BY EMPDEPT) AS
ranking,
EMPDEPT,
EMPID
FROM
test
WHERE EMPID = '111')
)
GROUP BY EMPID
But it has few drawbacks :
Not sure rank() is ANSI SQL standard. If not it will not be supported in non-oracle databases.
This will work only if you know the maximum number of departments an employee can work in.
Query is may require some fine tuning.