SQL : How to create one table with use pivot or case-CodePudding

I have this table:

ID	condition	start time	end time
M01	baseline	15/06/2018	16/04/2015
M01	tactical	03/04/2001	06/12/2017
M02	tactical	30/06/2007	18/08/2002
M02	baseline	19/07/2001	06/09/2007
M03	baseline	09/12/2015	30/07/2014
M03	tactical	17/07/2002	09/05/2009

I will obtain this table witch condition on "baseline" or "tactical" :

ID	baseline start time	baseline end time	tactical start time	tactical end time
M01	15/06/2018	16/04/2015	03/04/2001	06/12/2017
M02	------	--------	-------	-------
M03	------	--------	-------	-------

I would like to use pivot or case in SQL. Thanks.

CodePudding user response：

There are multiple ways to do this and probably the old case when is the easiest to do:

SELECT id, 
    MAX(CASE WHEN condition = 'baseline' THEN [start time] END) [baseline start time],
    MAX(CASE WHEN condition = 'baseline' THEN [end time] END) [baseline end time],
    MAX(CASE WHEN condition = 'tactical' THEN [start time] END) [tactical start time],
    MAX(CASE WHEN condition = 'tactical' THEN [end time] END) [tactical end time]
  FROM myTable
  GROUP BY id;

DBFiddle demo

Yet another way:

WITH ids AS (SELECT DISTINCT ID FROM myTable)
SELECT *
FROM ids i
     CROSS APPLY(SELECT [start time], [end time]
                 FROM myTable t
                 WHERE i.ID=t.ID AND t.condition='baseline') bl([baseline start time], [baseline end time])
     CROSS APPLY(SELECT [start time], [end time]
                 FROM myTable t
                 WHERE i.ID=t.ID AND t.condition='tactical') tc([tactical start time], [tactical end time]);