Transpose Column and clear partial rows

Database
Visitor

Transpose Column and clear partial rows

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. 

 

Select

EMP.Name, Start_Dt, Work_ID, TASK.TASK_IDS

From Emp

Left Join

Task on  EMP.Name=TASK.Name

 

 

 Name  Start_Dt Work_ID Task_IDs
Jim 1/1/2017JP3323T667
Jim 1/1/2017JP3323T693
Jim 1/1/2017JP3323T789
Jim 1/1/2017jp3233T998
Dave 2/21/2017DW7844R221
Dave 2/21/2017DW7844R465
Dave 2/21/2017DW7844R643
Dave 2/21/2017DW7844R776

 

 

 

 Name  Start Dt Work ID Task IDs
Jim 1/1/2017JP3323T667
   T693
   T789
   T998
Dave 2/21/2017DW7844R221
   R465
   R643
   R776
3 REPLIES
Teradata Employee

Re: Transpose Column and clear partial rows

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.

Junior Contributor

Re: Transpose Column and clear partial rows

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
New Member

Re: Transpose Column and clear partial rows

455