title: Will I need to create a pivot or can I alter this query / have added link to test data
body: <p>The following Query that I wrote is working to a point.  The problem that I found is that in each pump, cable, etc can be listed  in each of the part columns.  Example:</p>
  
  <p><a href="https://i.stack.imgur.com/MvxTs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/MvxTs.png" alt="enter image description here"></a></p>
  
  <p>What I would like to get is the well listed with each of the pumps, cables, etc that are there instead of what I am currently getting which is just the first pump, cable, etc.</p>
  
  <p>Here is the query I am running:</p>
  
  <pre><code>        With CTE AS(
      SELECT Pull_Date, Well_Name, Event_Num, Part1 Part, Part1_PN PartPN, Part1_SN PartSN FROM test
      UNION ALL
          SELECT Pull_Date, Well_Name, Event_Num, Part2, Part2_PN, Part2_SN from test
      UNION ALL
          SELECT Pull_Date, Well_Name, Event_Num, Part3, Part3_PN, Part3_SN from test
  UNION ALL
          SELECT Pull_Date, Well_Name, Event_Num, Part4, Part4_PN, Part4_SN from test
  UNION ALL
          SELECT Pull_Date, Well_Name, Event_Num, Part5, Part5_PN, Part5_SN from test
  UNION ALL
          SELECT Pull_Date, Well_Name, Event_Num, Part6, Part6_PN, Part6_SN from test
      )
  
      Select Pull_Date, Well_Name,
          MIN(CASE WHEN Part='BODH' THEN 'BODH' ELSE NULL END) [BODH],
          MIN(CASE WHEN Part='BODH' THEN PartPN Else NULL END) BODH_PN,
          MIN(CASE WHEN Part='BODH' THEN PartSN ELSE NULL END) BODH_SN,
          MIN(CASE WHEN Part='Cable' THEN 'Cable' ELSE NULL END) [Cable],
          MIN(CASE WHEN Part= 'Cable' THEN PartPN Else NULL END) Cable_PN,
          MIN(CASE WHEN Part= 'Cable' THEN PartSN ELSE NULL END) Cable_SN,
          MIN(CASE WHEN Part= 'Pump' THEN 'Pump' ELSE Null END) [Pump],
          MIN(CASE WHEN Part= 'Pump' THEN PartPN ELSE NULL END) Pump_PN,
          MIN(CASE WHEN Part= 'Pump' THEN PartSN ELSE NULL END) Pump_SN,
          MIN(CASE WHEN Part= 'MLE' THEN 'MLE' ELSE NULL END) [MLE],
          MIN(CASE WHEN Part= 'MLE' THEN PartPN ELSE NULL END) MLE_PN,
          MIN(CASE WHEN Part= 'MLE' THEN PartSN ELSE NULL END) MLE_SN,
          MIN(CASE WHEN Part= 'AGH' THEN 'AGH' ELSE NULL END) [AGH],
          MIN(CASE WHEN Part= 'AGH' THEN PartPN ELSE NULL END) AGH_PN,
          MIN(CASE WHEN Part= 'AGH' THEN PartSN ELSE NULL END) AGH_SN,
          MIN(CASE WHEN Part= 'Sensor' THEN 'Sensor' ELSE NULL END) [Sensor],
          MIN(CASE WHEN Part='Sensor' THEN PartPN ELSE NULL END) Sensor_PN,
          MIN(CASE WHEN Part='Sensor' THEN PartSN ELSE NULL END) Sensor_SN,
          MIN(CASE WHEN Part='Intake' THEN 'Intake' ELSE NULL END) [Intake],
          MIN(CASE WHEN Part='Intake' THEN PartPN ELSE NULL END) Intake_PN,
          MIN(CASE WHEN Part='Intake' THEN PartSN ELSE NULL END) Intake_SN
      FROM CTE
      GROUP BY Pull_Date, Well_Name
  </code></pre>
  
  <p>And here is an example of what I am currently receiving:</p>
  
  <p><a href="https://i.stack.imgur.com/b8P3y.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/b8P3y.png" alt="enter image description here"></a></p>
  
  <p>I am not very familiar with SQL Server PIVOT or if this is even needed in this case.  </p>
  
  <p>I appreciate any assistance on this and thank you in advance.</p>
  
  <p>EDIT:</p>
  
  <p>Unfortunately I cannot add a picture of what I would like so I will try to explain it.</p>
  
  <p>I would like to have it all in one row so it would look somewhat like this:</p>
  
  <pre><code>Pull Date| Well_Name | BODH1 | BODH1_PN | BODH1_SN | BODH2 | BODH2_PN | BODH2_SN
  </code></pre>
  
  <p>Pump, Cable, MLE, etc would also repeat.  I have 20 part columns and would like to pull them so I could easily sort the pump1 through possibly pump5 (some wells have 5 some less).  Eventually this data will be inserted into another table to update it.  </p>
  
  <p>EDIT 2:
  Link to test data I created <a href="http://sqlfiddle.com/#!3/4f6bd" rel="nofollow noreferrer">Test Database on SQLFiddle</a></p>