title: How to construct query to change the output of an sql table
body: <p>I need some  little help here...So, let me introduce you my problem.</p>
  
  <p>I have the following SQL Server table: </p>
  
  <pre><code>| RankCode | SeaPortInd | WatchKeepingInd |      EffectiveDate      | VesselCode |        FromDate         |         ToDate          |        LastDate         | LastUser |
  +----------+------------+-----------------+-------------------------+------------+-------------------------+-------------------------+-------------------------+----------+
  | C/E      |          0 |               0 | 1900-01-01 00:00:00.000 |        031 | 1900-01-01 05:00:00.000 | 1900-01-01 07:00:00.000 | 2016-08-11 12:40:00.000 | d.baltas |
  | C/E      |          0 |               0 | 2016-06-02 00:00:00.000 |        031 | 1900-01-01 00:00:00.000 | 1900-01-01 00:00:00.000 | 1900-01-01 00:00:00.000 | d.baltas |
  | C/E      |          0 |               1 | 2016-06-01 00:00:00.000 |        031 | 1900-01-01 01:00:00.000 | 1900-01-01 02:00:00.000 | 2016-08-11 17:58:00.000 | d.baltas |
  | C/E      |          0 |               1 | 2016-06-02 00:00:00.000 |        031 | 1900-01-01 01:00:00.000 | 1900-01-01 02:00:00.000 | 2016-08-10 17:58:00.000 | d.baltas |
  | C/E      |          1 |               1 | 2016-06-01 00:00:00.000 |        031 | 1900-01-01 03:00:00.000 | 1900-01-01 04:00:00.000 | 2016-08-10 17:58:00.000 | d.baltas |
  | MSTR     |          0 |               0 | 2016-06-02 00:00:00.000 |        031 | 1900-01-01 16:00:00.000 | 1900-01-01 22:00:00.000 | 2016-08-10 17:58:00.000 | d.baltas |
  | MSTR     |          0 |               1 | 2016-06-01 00:00:00.000 |        031 | 1900-01-01 08:00:00.000 | 1900-01-01 12:00:00.000 | 2016-08-10 17:58:00.000 | d.baltas |
  | MSTR     |          1 |               0 | 2016-06-03 00:00:00.000 |        031 | 1900-01-01 08:00:00.000 | 1900-01-01 14:00:00.000 | 2016-08-11 15:00:00.000 | d.baltas |
  +----------+------------+-----------------+-------------------------+------------+-------------------------+-------------------------+-------------------------+----------+
  </code></pre>
  
  <p>I want to take an output like this table: </p>
  
  <p><a href="https://i.stack.imgur.com/FkVjb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FkVjb.png" alt="enter image description here"></a></p>
  
  <p><strong>Some more explanation of table:</strong>  </p>
  
  <p>Scheduled daily work hours at sea means SeaPortInd = 1</p>
  
  <p>Scheduled daily work hours at port means  SeaPortInd = 0</p>
  
  <p>Watchkeeping means WatchkeepingInd = 1</p>
  
  <p>NonWatchkeeping means WatchkeepingInd = 0</p>
  
  <p>I managed to take to the following table:</p>
  
  <pre><code>+----------+--------------------+
  | RankCode | SeaNonWatchkeeping |
  | C/E      |  00:00 - 00:00     |
  |          |  05:00 - 07:00     |
  | MSTR     |  16:00 - 22:00     |
  +----------+--------------------+
  </code></pre>
  
  <p>with the query: </p>
  
  <pre><code>SELECT CASE 
          WHEN row_number() OVER (
                  PARTITION BY RankCode ORDER BY FromDate asc
      ) = 1
              THEN RankCode
          ELSE ''
          END AS RankCode
  
      ,substring(convert(VARCHAR(255), FromDate, 120), 11, 6) + ' -' + substring(convert(VARCHAR(255), ToDate, 120), 11, 6) AS SeaNonWatchkeeping
  
  FROM WorkingHoursSchedule WHERE SeaPortInd = 0 AND watchkeepingind = 0
  </code></pre>
  
  <p>Can you please help me how to get the cases SeaportInd = 0 and Watchkeeping= 1 etc? </p>
  
  <p>I am using SQL Server 2008 but the query will also run at some previous versions with minimum SQL Server 2005</p>
  
  <p>Thanks in advance!</p>