In oracle, query is resturning this dataset
DEPT | EMP_NAME | SALARY -----+----------+------- 10 | MARY | 30000 10 | JOHN | 20000 10 | SCOTT | 20000 20 | BOB | 50000 20 | BETTY | 50000
my objective is to make it as below
DEPT | EMP_NAME | SALARY -----+----------+------- 10 | MARY | 30000 | JOHN | 20000 | SCOTT | 20000 20 | BOB | 50000 | BETTY | 50000
dep name should appear at once until new dep start.
Assuming your results are ordered by department, employee name, you could use the LAG function to find the first row for each department. You'd need to check each row to see if it has a different department than the previous row, something like this:
SELECT CASE /* Check if there's a different department number on this row compared to the previous row */ WHEN NVL(LAG(t.dept) OVER (ORDER BY t.dept, t.emp_name),-1) <> t.dept /* If previous row is null (ie, this is the first row), use -1 as previous dept number so comparison does not fail */ THEN t.dept END AS display_dept, t.emp_name, t.salary FROM myTable t ORDER BY t.dept, t.emp_name That said, as others have pointed out, this really isn't something SQL is meant to do; you'd be much better off having this logic be in whatever is displaying the query results than in the query itself.
