title: Pivot tables with related columns
body: <p>This seems to me to be a trivial task, but due to (probably) lack of understanding the pivoting mechanism, I am having trouble finding a solution to it.</p>
  
  <p>I have the following table with sample data:</p>
  
  <pre><code>id      code    created_datetime    sourceID
  ------  ----    ------------------- --------
  481792  DEF     2016-12-04 18:31:00 398464
  481792  JKH     2016-12-04 18:31:00 398464
  486429  ABC     2010-09-07 09:29:00 302304
  486429  DEF     2009-02-05 03:04:00 320903
  486429  GHI     2009-02-05 03:04:00 320903
  </code></pre>
  
  <p>What I would like to get as a result is a pivot on the 'code' column, an indication of whether or not this code exists for an Id. The column 'created_datetime' I am trying to convert with SELECT DATEDIFF(DAY,created_datetime,CURRENT_TIMESTAMP) into a count of days up till now - and finally the sourceID. I had something like this in mind for the result (I really hope this is formatted correctly):</p>
  
  <pre><code>id      ABC ABC_sou ABC_days_active DEF DEF_sou DEF_days_active GHI GHI_sou GHI_days_active JKH JKH_sou JKH_days_active
  ------  --- ------- --------------- --- ------- --------------- --- ------- --------------- --- ------- ---------------
  481792  0                           1   398464  211                                         1   398464  211
  486429  1   302304  2491            1   320903  3070            1   320903  3070
  </code></pre>
  
  <p>One line pr. id, that is. </p>
  
  <p>What I have attempted so far, without quite getting there, is inspired by this link:<a href="https://stackoverflow.com/questions/21245893/pivot-on-multiple-columns">PIVOT on Multiple Columns</a>, and using the example here, i came up with this:</p>
  
  <pre><code>select id,
    [ABC], [ABC_source], [ABC_days_active],
    [DEF], [DEF_source], [DEF_days_active],
    [GHI], [GHI_source], [GHI_days_active],
    [JKH], [JKH]_source], [JKH_days_active]
  from
  (
    SELECT  id, 
                  code,
                  source,
                  col, value
          FROM MyTable
    cross apply
    (
      values
        ([code], CASE code WHEN NULL THEN 0 ELSE 1 END),
        ([code]+'_source', [source]),
        ([code]+'_date', CAST(DATEDIFF(DAY,created_datetime,CURRENT_TIMESTAMP) AS varchar (20)))
    ) c (col, value)
  ) d
  pivot
  (
    max(value)
    for col in ([ABC], [ABC_source], [ABC_date], [DEF], [DEF_source], [DEF_date],
                [GHI], [GHI_source], [GHI_date], [JKH], [JKH_source], [JKH_date])
  ) piv;
  </code></pre>
  
  <p>That gave me the correct columns (column names), but I still have the same number of rows as I started with. (And besides, 0 will only show as NULL, but I can live with that.)</p>
  
  <p>Can you give me a hint to get me there? It is probably something I have just not interpreted correctly...</p>