Home > Software design >  SQL : How to create one table with use pivot or case
SQL : How to create one table with use pivot or case

Time:07-16

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]);
  • Related