Hey Guys this ones been killing me and frankly, I have no idea if its even possible in code. Im trying to move some columns down into a new row from a join and not have any of the data repeat below. Lets see if I can illustrate this. The left table is the current "Result" and my expected result is below it.
This is the code.
EMP.Name, Start_Dt, Work_ID, TASK.TASK_IDS
Task on EMP.Name=TASK.Name
|Name||Start Dt||Work ID||Task IDs|
May be easier to do this in a reporting tool, or BTEQ ".SUPPRESS", but for SQL you could use something like:
CASE WHEN EMP.Name = MAX(EMP.Name) OVER (ORDER BY EMP.Name, Start_Dt, Work_ID, TASK.TASK_IDS
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) THEN '' ELSE EMP.Name END
For the date field, you'd have to CAST to a character string in the ELSE clause, so the data type matches.
As Fred said, display should be handled on the client side.
You could calculate a ROW_NUMBER once to display the first row only:
Select case when rn = 1 then Name end, case when rn = 1 then Start_Dt end), etc. from ( Select EMP.Name, Start_Dt, Work_ID, TASK.TASK_IDS, ROW_NUMBER() OVER (PARTITION BY EMP.Name ORDER BY TASK.TASK_IDS) as rn From Emp Left Join Task on EMP.Name=TASK.Name ) as dt
Probably be more efficient to calculate the ROW_NUMBER before joining:
Select case when rn = 1 then Emp.Name end, case when rn = 1 then Start_Dt end), etc. from Emp Left Join ( Select Name, Start_Dt, Work_ID, TASK_IDS, ROW_NUMBER() OVER (PARTITION BY EMP.Name ORDER BY TASK.TASK_IDS) as rn From Tasks ) Task on EMP.Name=TASK.Name