title: How to put data from a specific column into an one unique row?
body: <p>I have a table named dbo.build_main_process with data such as a transaction table with three or can be four different date for the same record. Sometimes the date retun null.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Product</th>
  <th>ProductCode</th>
  <th>OperatorName</th>
  <th>Step</th>
  <th>StepDate</th>
  <th>BuildLocatoin</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>0_Start</td>
  <td>2019-05-02</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>1_Material</td>
  <td>2019-05-03</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>3_Build</td>
  <td>2019-05-06</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>0_Start</td>
  <td>2019-05-03</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>1_Material</td>
  <td>2019-05-06</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>3_Build</td>
  <td>2019-05-07</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>0_Start</td>
  <td>2019-05-03</td>
  <td>AR</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>1_Material</td>
  <td>null</td>
  <td>AR</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>3_Build</td>
  <td>2019-05-06</td>
  <td>AR</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>0_Start</td>
  <td>2019-05-07</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>1_Material</td>
  <td>2019-05-08</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>3_Build</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>John Doe</td>
  <td>0_Start</td>
  <td>null</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>John Doe</td>
  <td>1_Material</td>
  <td>2019-05-07</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>John Doe</td>
  <td>3_Build</td>
  <td>2019-05-09</td>
  <td>MX</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I need to select data kind a &quot;Excel pivot&quot; for date in columns with unique rows per record:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Product</th>
  <th>ProductCode</th>
  <th>OperatorName</th>
  <th>0_Start_Date</th>
  <th>1_Material_Date</th>
  <th>3_Build_Date</th>
  <th>BuildLocatoin</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>2019-05-02</td>
  <td>2019-05-03</td>
  <td>2019-05-06</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>2019-05-03</td>
  <td>2019-05-06</td>
  <td>2019-05-07</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>2019-05-03</td>
  <td>null</td>
  <td>2019-05-06</td>
  <td>AR</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>2019-05-07</td>
  <td>2019-05-08</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>John Doe</td>
  <td>null</td>
  <td>2019-05-07</td>
  <td>2019-05-09</td>
  <td>MX</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I'm trying to use &quot;join&quot; in the same table to get unique records with date in columns:</p>
  <pre><code>select
      t1.Product
      ,t1.ProductCode
      ,t1.OperatorName
      ,case when t1.Step = '0_Start' then StepDate end as 0_Start_Date
      ,case when t2.Step = '1_Material' then StepDate end as 1_Material_Date  
      ,case when t3.Step = '3_Build' then StepDate end as 3_Build_Date
      ,BuildLocatoin  
  from 
      dbo.build_main_process as t1
      inner join dbo.build_main_process as t2
          on t1.ProductCode = t2.ProductCode 
          and t1.OperatorName = t2.OperatorName
      inner join dbo.build_main_process as t3
          on t1.ProductCode = t3.ProductCode 
          and t1.OperatorName = t3.OperatorName
  go
  </code></pre>
  <p>I'm not sure why the result not show unique rows, my result is very similar to this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Product</th>
  <th>ProductCode</th>
  <th>OperatorName</th>
  <th>0_Start_Date</th>
  <th>1_Material_Date</th>
  <th>3_Build_Date</th>
  <th>BuildLocatoin</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>null</td>
  <td>null</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>2019-05-02</td>
  <td>2019-05-03</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>2019-05-02</td>
  <td>2019-05-03</td>
  <td>2019-05-06</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>2019-05-02</td>
  <td>2019-05-03</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>null</td>
  <td>2019-05-03</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>null</td>
  <td>2019-05-03</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Smith</td>
  <td>null</td>
  <td>2019-05-03</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>null</td>
  <td>null</td>
  <td>null</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>2019-05-03</td>
  <td>null</td>
  <td>null</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>2019-05-03</td>
  <td>2019-05-06</td>
  <td>2019-05-07</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>2019-05-03</td>
  <td>2019-05-06</td>
  <td>2019-05-07</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>2019-05-03</td>
  <td>null</td>
  <td>2019-05-07</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Table</td>
  <td>Table_2350</td>
  <td>John Doe</td>
  <td>null</td>
  <td>null</td>
  <td>null</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>null</td>
  <td>null</td>
  <td>null</td>
  <td>AR</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>2019-05-03</td>
  <td>null</td>
  <td>null</td>
  <td>AR</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>2019-05-03</td>
  <td>null</td>
  <td>2019-05-06</td>
  <td>AR</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>2019-05-03</td>
  <td>null</td>
  <td>2019-05-06</td>
  <td>AR</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>2019-05-03</td>
  <td>null</td>
  <td>2019-05-06</td>
  <td>AR</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>null</td>
  <td>null</td>
  <td>null</td>
  <td>AR</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>Joseph Doe</td>
  <td>null</td>
  <td>null</td>
  <td>null</td>
  <td>AR</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>null</td>
  <td>null</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>null</td>
  <td>null</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>null</td>
  <td>null</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>2019-05-07</td>
  <td>null</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>2019-05-07</td>
  <td>2019-05-08</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>2019-05-07</td>
  <td>2019-05-08</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>null</td>
  <td>null</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>null</td>
  <td>2019-05-08</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Yellow_Chair</td>
  <td>Joseph Smith</td>
  <td>null</td>
  <td>null</td>
  <td>null</td>
  <td>ZA</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>John Doe</td>
  <td>null</td>
  <td>2019-05-07</td>
  <td>2019-05-09</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>John Doe</td>
  <td>null</td>
  <td>2019-05-07</td>
  <td>null</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>John Doe</td>
  <td>null</td>
  <td>2019-05-07</td>
  <td>2019-05-09</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>John Doe</td>
  <td>null</td>
  <td>2019-05-07</td>
  <td>2019-05-09</td>
  <td>MX</td>
  </tr>
  <tr>
  <td>Chair</td>
  <td>Black_Chair</td>
  <td>John Doe</td>
  <td>null</td>
  <td>2019-05-07</td>
  <td>null</td>
  <td>MX</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Below the DDL:</p>
  <p>---Create table</p>
  <pre><code>create table [dbo].[build_process](
      [Product] varchar(50) NOT NULL,
      [ProductCode] varchar(50) NOT NULL,
      [OperatorName] varchar(50) NOT NULL,
      [Step] varchar(15) NULL,
      [StepDate] date NULL,
      [StepTimeinDays] int NULL,
      [BuildLocation] varchar(2) NULL
  ) on [primary]
  go
  </code></pre>
  <p>---Populate table</p>
  <pre><code>insert into [dbo].[build_main_process] values
      ('Chair','Black_Chair','Joseph Smith','0_Start','2019-05-02','ZA'),
      ('Chair','Black_Chair','Joseph Smith','1_Material','2019-05-03','ZA'),
      ('Chair','Black_Chair','Joseph Smith','3_Build','2019-05-06','ZA'),
      ('Table','Table_2350','John Doe','0_Start','2019-05-03','MX'),
      ('Table','Table_2350','John Doe','1_Material','2019-05-06','MX'),
      ('Table','Table_2350','John Doe','3_Build','2019-05-07','MX'),
      ('Chair','Black_Chair','Joseph Doe','0_Start','2019-05-03','AR'),
      ('Chair','Black_Chair','Joseph Doe','1_Material',null,'AR'),
      ('Chair','Black_Chair','Joseph Doe','3_Build','2019-05-06','AR'),
      ('Chair','Yellow_Chair','Joseph Smith','0_Start','2019-05-07','ZA'),
      ('Chair','Yellow_Chair','Joseph Smith','1_Material','2019-05-08','ZA'),
      ('Chair','Yellow_Chair','Joseph Smith','3_Build',null,'ZA'),
      ('Chair','Black_Chair','John Doe','0_Start',null,'MX'),
      ('Chair','Black_Chair','John Doe','1_Material','2019-05-07','MX'),
      ('Chair','Black_Chair','John Doe','3_Build','2019-05-09','MX')
  go
  </code></pre>