title: How to PIVOT this data in SQL Server?
body: <p>I have this query below </p>
  
  <pre><code>select * from (
      SELECT tjdm.ID as id,  cjb.CORPORATE_ID, 
          tjdm.JOB_TICKET_VALUE, cjb.DISPLAY_NAME
          from Cjd cjb
          join Tjdm tjdm 
          on cjb.CUSTOM_JOB_DATA_ID = tjdm.CUSTOM_JOB_DATA_ID
          where cjb.CUSTOM_DATA_TYPE in (1,2) and cjb.DISPLAY_IS_ACTIVE = 1
      ) AS PivotData
  PIVOT (
      max(JOB_TICKET_VALUE)
      FOR DISPLAY_NAME IN
          ([OMXAccount],[Consignee],[Col1],[Col2],[Col3],[Col4])
  ) AS PivotTable
  </code></pre>
  
  <p>The <code>select * from (.....)</code> returns a data columns as shown below. </p>
  
  <pre><code>id  CORPORATE_ID  JOB_TICKET_VALUE  DISPLAY_NAME
  --  ------------  ----------------  ------------
  53  1             9                 OMXAccount
  53  1             199               Consignee
  54  6             "No value"        OMXAccount
  54  6             "No value"        Consignee
  58  1             "No value"        OMXAccount
  58  1             "No value"        Consignee
  </code></pre>
  
  <p>But when the "PIVOT" statement being executed, I am getting this result.</p>
  
  <pre><code>id  CORPORATE_ID  OMXAccount  Consignee  [Col1]  [Col2]  [Col3]  [Col4]
  --  ------------  ----------  ---------  ------  ------  ------  ------
  53  1             9           199        Null    Null    Null    Null
  54  6                                    Null    Null    Null    Null
  58  1                                    Null    Null    Null    Null
  </code></pre>
  
  <p>The expected output should be just like below.</p>
  
  <pre><code>id  CORPORATE_ID  OMXAccount  Consignee  [Col1]  [Col2]  [Col3]  [Col4]
  --  ------------  ----------  ---------  ------  ------  ------  ------
  53  1             OMXAccount  Consignee  Null    Null    Null    Null
  54  6             OMXAccount  Consignee  Null    Null    Null    Null
  58  1             OMXAccount  Consignee  Null    Null    Null    Null
  </code></pre>
  
  <p>What is wrong?</p>
  
  <p>Thanks </p>
  
  <p>RJuy</p>