Transpose results

  • I wish to transpose my results so that I see the 'Start' and 'Finish' date on the same row for a particular ProgressGroup.

    Assumptions: There will always be a 'Start' ProgressMessage for a ProgressGroup, but not always a 'Finish' ProgressMessage for the same ProgressGroup

    CREATE TABLE Progress

    (

    ProgressID INT,

    ProgressSource VARCHAR(20),

    ProgressMessage VARCHAR(20),

    ProgressDate SmallDateTime,

    ProgressGroup VARCHAR(20)

    )

    ;

    INSERT INTO Progress

    VALUES

    (1,'SSIS1','Start','2010-01-01 09:00:00','111-111-111'),

    (2,'SSIS1','Finished','2010-01-01 09:05:0','111-111-111'),

    (3,'SSIS1','Start','2010-01-01 09:12:00','222-222-222'),

    (4,'SSIS1','Finished','2010-01-01 09:13:00','222-222-222'),

    (5,'SSIS1','Start','2010-01-01 09:15:00','333-333-333'),

    (6,'SSIS1','Finished','2010-01-01 09:20:00','333-333-333'),

    (7,'SSIS1','Start','2010-01-01 09:25:00','444-444-444')

    ;

    SELECT * FROM Progress;

    I wish to turn my results into the below:

    Progress GroupStartedFinished

    111-111-1112010-01-01 09:00:002010-01-01 09:05:00

    222-222-2222010-01-01 09:12:002010-01-01 09:13:00

    ......

    444-444-4442010-01-01 09:25:00

    Any help would be greatly appreciated.

    Many Thanks

  • I am thinking what you need is this:

    select ProgressGroup,

    min(ProgressDate) as Started,

    case when max(ProgressDate) = min(ProgressDate) then ' ' else max(ProgressDate) end as Finished

    from Progress where ProgressMessage in ('Start','Finished')

    group by ProgressGroup,

    order by ProgressGroup

    The probability of survival is inversely proportional to the angle of arrival.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply