Transpose results

• squidder11

  SSC-Addicted

  Points: 463

  More actions

  October 27, 2010 at 2:26 pm

  #230471

  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

• sturner

  SSC-Insane

  Points: 22411

  More actions

  October 27, 2010 at 2:45 pm

  #1243194

  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.