How to output into different rows

Damien 41 Reputation points
2022-11-15T09:48:03.123+00:00

I have a built a SQL view that outputs the following data

Reference Description Operation Operation Description SequenceNumber Phase Hours Minutes Seconds
2661 CENTRE FRAME - WELD MAR MARSHALLING 1.000000 Setup 0 0 0
2661 CENTRE FRAME - WELD SAW2P SAWING FRAME PLATES 10.000000 Runtime 0 7 30
2661 CENTRE FRAME - WELD SAW2P SAWING FRAME PLATES 10.000000 Setup 0 0 0
2661 CENTRE FRAME - WELD WCOM WELD COMM. FRAMES 20.000000 Runtime 0 45 0

But I would like it to look like

Reference Description Operation Operation Description SequenceNumber Phase Hours Minutes Seconds Phase Hours Minutes Seconds
2661 CENTRE FRAME - WELD MAR MARSHALLING 1.000000 Setup 0 0 0
2661 CENTRE FRAME - WELD SAW2P SAWING FRAME PLATES 10.000000 Setup 0 0 0 Runtime 0 7 30
2661 CENTRE FRAME - WELD WCOM WELD COMM. FRAMES 20.000000 Runtime 0 45 0

So I basically want to combine entries where the sequence number is the same and shift the Runtime Hours/Minutes/Seconds to the end of the Setup Phase.

I don't really understand the logic that I need to apply to make it happen.

Hopefully that makes sense to someone.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Viorel 114.7K Reputation points
    2022-11-15T11:48:39.693+00:00

    Try creating a new view like this:

    select a.Reference, a.Description, a.Operation, a.[Operation Description], a.SequenceNumber,   
    	a.Phase as Phase1, a.Hours as Hours1, a.Minutes as Minutes1, a.Seconds as Seconds1,  
    	b.Phase as Phase2, b.Hours as Hours2, b.Minutes as Minutes2, b.Seconds as Seconds2  
    from MyFirstView a  
    left join MyFirstView b on b.Reference = a.Reference and b.Operation = a.Operation and b.Phase <> a.Phase  
    where a.Phase = 'Setup'  
    or b.Reference is null  
    

    Or adjust the existing view.

    If it does not work, then give more clear details about your columns.

    0 comments No comments