Will I need to create a pivot or can I alter this query / have added link to test data

Question

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:

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.

Here is the query I am running:

        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

And here is an example of what I am currently receiving:

I am not very familiar with SQL Server PIVOT or if this is even needed in this case.

I appreciate any assistance on this and thank you in advance.

EDIT:

Unfortunately I cannot add a picture of what I would like so I will try to explain it.

I would like to have it all in one row so it would look somewhat like this:

Pull Date| Well_Name | BODH1 | BODH1_PN | BODH1_SN | BODH2 | BODH2_PN | BODH2_SN

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.

EDIT 2: Link to test data I created Test Database on SQLFiddle

Create a sample data here and it will be easier to help. http://sqlfiddle.com/ — FutbolFan, Aug 15 '15 at 04:25
Thanks for providing the sample data! But, it's not clear what your expected output would be from all this. — FutbolFan, Aug 15 '15 at 23:02
Thank you for looking at it again. Instead of what I get when using the similar query above which is only grabbing one pump for the row. I want all pumps to show. In the edit 1 it shows something like I would like. Where it sorts it and puts in pump1, pump2, pump3, etc. Whereas in the query that I wrote I am only grabbing one pump from all the data. Instead of only getting one pump, motor, etc per row I want to get them all but to have them labeled with pump1, pump2, motor1, motor2, etc instead of just part1. — Pete, Aug 16 '15 at 15:30
You need a dynamic pivot query I think. You can probably take a look at this: http://stackoverflow.com/questions/18657214/sql-server-dynamic-pivot-over-5-columns — FutbolFan, Aug 16 '15 at 21:31

score 1 · Accepted Answer · answered Aug 18 '15 at 16:00

I will be looking into Dynamic Pivot for later uses, but this solved my problem (Yes, it is very boring and ugly):

Select Pull_Date, Well_Name,
          MIN(CASE WHEN Part1 ='BODH' THEN 'BODH' ELSE NULL END) [BODH1],
          MIN(CASE WHEN Part1 ='BODH' THEN Part1_PN Else NULL END) BODH1_PN,
          MIN(CASE WHEN Part1 ='BODH' THEN Part1_SN ELSE NULL END) BODH1_SN,
          MIN(CASE WHEN Part2 ='BODH' THEN 'BODH' ELSE NULL END) [BODH2],
          MIN(CASE WHEN Part2 ='BODH' THEN Part2_PN Else NULL END) BODH2_PN,
          MIN(CASE WHEN Part2 ='BODH' THEN Part2_SN ELSE NULL END) BODH2_SN,
          MIN(CASE WHEN Part3 ='BODH' THEN 'BODH' ELSE NULL END) [BODH3],
          MIN(CASE WHEN Part3 ='BODH' THEN Part3_PN Else NULL END) BODH3_PN,
          MIN(CASE WHEN Part3 ='BODH' THEN Part3_SN ELSE NULL END) BODH3_SN,
          MIN(CASE WHEN Part4 ='BODH' THEN 'BODH' ELSE NULL END) [BODH4],
          MIN(CASE WHEN Part4 ='BODH' THEN Part4_PN Else NULL END) BODH4_PN,
          MIN(CASE WHEN Part4 ='BODH' THEN Part4_SN ELSE NULL END) BODH4_SN,
          MIN(CASE WHEN Part5 ='BODH' THEN 'BODH' ELSE NULL END) [BODH5],
          MIN(CASE WHEN Part5 ='BODH' THEN Part5_PN Else NULL END) BODH5_PN,
          MIN(CASE WHEN Part5 ='BODH' THEN Part5_SN ELSE NULL END) BODH5_SN,
          MIN(CASE WHEN Part1 ='Cable' THEN 'Cable' ELSE NULL END) [Cable1],
          MIN(CASE WHEN Part1 ='Cable' THEN Part1_PN Else NULL END) Cable1_PN,
          MIN(CASE WHEN Part1 ='Cable' THEN Part1_SN ELSE NULL END) Cable1_SN,
          MIN(CASE WHEN Part2 ='Cable' THEN 'Cable' ELSE NULL END) [Cable2],
          MIN(CASE WHEN Part2 ='Cable' THEN Part2_PN Else NULL END) Cable2_PN,
          MIN(CASE WHEN Part2 ='Cable' THEN Part2_SN ELSE NULL END) Cable2_SN,
          MIN(CASE WHEN Part3 ='Cable' THEN 'Cable' ELSE NULL END) [Cable3],
          MIN(CASE WHEN Part3 ='Cable' THEN Part3_PN Else NULL END) Cable3_PN,
          MIN(CASE WHEN Part3 ='Cable' THEN Part3_SN ELSE NULL END) Cable3_SN,
          MIN(CASE WHEN Part4 ='Cable' THEN 'Cable' ELSE NULL END) [Cable4],
          MIN(CASE WHEN Part4 ='Cable' THEN Part4_PN Else NULL END) Cable4_PN,
          MIN(CASE WHEN Part4 ='Cable' THEN Part4_SN ELSE NULL END) Cable4_SN,
          MIN(CASE WHEN Part5 ='Cable' THEN 'Cable' ELSE NULL END) [Cable5],
          MIN(CASE WHEN Part5 ='Pump' THEN Part5_PN Else NULL END) Pump5_PN,
          MIN(CASE WHEN Part5 ='Pump' THEN Part5_SN ELSE NULL END) Pump5_SN,
          MIN(CASE WHEN Part1 ='Pump' THEN 'Pump' ELSE NULL END) [Pump1],
          MIN(CASE WHEN Part1 ='Pump' THEN Part1_PN Else NULL END) Pump1_PN,
          MIN(CASE WHEN Part1 ='Pump' THEN Part1_SN ELSE NULL END) Pump1_SN,
          MIN(CASE WHEN Part2 ='Pump' THEN 'Pump' ELSE NULL END) [Pump2],
          MIN(CASE WHEN Part2 ='Pump' THEN Part2_PN Else NULL END) Pump2_PN,
          MIN(CASE WHEN Part2 ='Pump' THEN Part2_SN ELSE NULL END) Pump2_SN,
          MIN(CASE WHEN Part3 ='Pump' THEN 'Pump' ELSE NULL END) [Pump3],
          MIN(CASE WHEN Part3 ='Pump' THEN Part3_PN Else NULL END) Pump3_PN,
          MIN(CASE WHEN Part3 ='Pump' THEN Part3_SN ELSE NULL END) Pump3_SN,
          MIN(CASE WHEN Part4 ='Pump' THEN 'Pump' ELSE NULL END) [Pump4],
          MIN(CASE WHEN Part4 ='Pump' THEN Part4_PN Else NULL END) Pump4_PN,
          MIN(CASE WHEN Part4 ='Pump' THEN Part4_SN ELSE NULL END) Pump4_SN,
          MIN(CASE WHEN Part5 ='Pump' THEN 'Pump' ELSE NULL END) [Pump5],
          MIN(CASE WHEN Part5 ='Pump' THEN Part5_PN Else NULL END) Pump5_PN,
          MIN(CASE WHEN Part5 ='Pump' THEN Part5_SN ELSE NULL END) Pump5_SN,
          MIN(CASE WHEN Part1 ='Sensor' THEN 'Sensor' ELSE NULL END) [Sensor1],
          MIN(CASE WHEN Part1 ='Sensor' THEN Part1_PN Else NULL END) Sensor1_PN,
          MIN(CASE WHEN Part1 ='Sensor' THEN Part1_SN ELSE NULL END) Sensor1_SN,
          MIN(CASE WHEN Part2 ='Sensor' THEN 'Sensor' ELSE NULL END) [Sensor2],
          MIN(CASE WHEN Part2 ='Sensor' THEN Part2_PN Else NULL END) Sensor2_PN,
          MIN(CASE WHEN Part2 ='Sensor' THEN Part2_SN ELSE NULL END) Sensor2_SN,
          MIN(CASE WHEN Part3 ='Sensor' THEN 'Sensor' ELSE NULL END) [Sensor3],
          MIN(CASE WHEN Part3 ='Sensor' THEN Part3_PN Else NULL END) Sensor3_PN,
          MIN(CASE WHEN Part3 ='Sensor' THEN Part3_SN ELSE NULL END) Sensor3_SN,
          MIN(CASE WHEN Part4 ='Sensor' THEN 'Sensor' ELSE NULL END) [Sensor4],
          MIN(CASE WHEN Part4 ='Sensor' THEN Part4_PN Else NULL END) Sensor4_PN,
          MIN(CASE WHEN Part4 ='Sensor' THEN Part4_SN ELSE NULL END) Sensor4_SN,
          MIN(CASE WHEN Part5 ='Sensor' THEN 'Sensor' ELSE NULL END) [Sensor5],
          MIN(CASE WHEN Part5 ='Sensor' THEN Part5_PN Else NULL END) Sensor5_PN,
          MIN(CASE WHEN Part5 ='Sensor' THEN Part5_SN ELSE NULL END) Sensor5_SN
 FROM Test
 GROUP BY Pull_Date, Well_Name

Will I need to create a pivot or can I alter this query / have added link to test data

1 Answers1