5

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:

enter image description here

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, Part1 Part, Part1_PN PartPN, Part1_SN PartSN FROM testtest
UNION ALL
    SELECT Pull_Date, Well_Name, Part2, Part2_PN, Part2_SN from testtest
UNION ALL
    SELECT Pull_Date, Well_Name, Part3, Part3_PN, Part3_SN from testtest
)

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
FROM CTE
GROUP BY Pull_Date, Well_Name

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

enter image description here

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: SQLFiddle

3
  • what is the structure of your table? can you add a sample of your desired output? Aug 14 '15 at 20:22
  • Julien, I have added a bit more information. If anything else is needed please let me know. I apologize for not being able to add a picture of what exactly is needed.
    – Pete
    Aug 15 '15 at 1:00
  • The PIVOT syntax is intended for pivoting a single column only. The alternative conditional aggregation method, which you are using, is much more flexible, as you've ascertained for yourself.
    – Andriy M
    Aug 15 '15 at 11:13
2

This should work. I took the original CTE, partitioned by part and date and numbered those. This gave every part on each date it's own number starting with 1.

Then in the case statement, adding WHERE Number = N allows it so that each part is unique.

Finally, the column names were modified to have their respective numbers.

Since this is unwieldy, dynamic sql could be used to generate the repetitive code.

    With CTE AS (SELECT * FROM 
(SELECT ROW_NUMBER() OVER(PARTITION BY Part + Pull_Date ORDER BY PartSN) AS [Number], 
  Pull_Date, 
  Well_Name, 
  Part, 
  PartSN, 
  PartPN 
FROM (
SELECT Pull_Date, Well_Name, Part1 Part, Part1_PN PartPN, Part1_SN PartSN 
  FROM test
UNION ALL
    SELECT Pull_Date, Well_Name, Part2, Part2_PN, Part2_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part3, Part3_PN, Part3_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part4, Part4_PN, Part4_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part5, Part5_PN, Part5_SN from test
UNION ALL
    SELECT Pull_Date, Well_Name, Part6, Part6_PN, Part6_SN from test
) subquery) new
  )

 Select Pull_Date, Well_Name,
    MIN(CASE WHEN Part='BODH' AND Number=1 THEN 'BODH' ELSE NULL END) [BODH1],
    MIN(CASE WHEN Part='BODH' AND Number=1 THEN PartPN Else NULL END) BODH_PN1,
    MIN(CASE WHEN Part='BODH' AND Number=1 THEN PartSN ELSE NULL END) BODH_SN1,
    MIN(CASE WHEN Part='BODH' AND Number=2 THEN 'BODH' ELSE NULL END) [BODH2],
    MIN(CASE WHEN Part='BODH' AND Number=2 THEN PartPN Else NULL END) BODH_PN2,
    MIN(CASE WHEN Part='BODH' AND Number=2 THEN PartSN ELSE NULL END) BODH_SN2,

    MIN(CASE WHEN Part='Cable' AND Number=1 THEN 'Cable' ELSE NULL END) [Cable1],
    MIN(CASE WHEN Part= 'Cable' AND Number=1 THEN PartPN Else NULL END) Cable_PN1,
    MIN(CASE WHEN Part= 'Cable' AND Number=1 THEN PartSN ELSE NULL END) Cable_SN1,
    MIN(CASE WHEN Part='Cable' AND Number=2 THEN 'Cable' ELSE NULL END) [Cable2],
    MIN(CASE WHEN Part= 'Cable' AND Number=2 THEN PartPN Else NULL END) Cable_PN2,
    MIN(CASE WHEN Part= 'Cable' AND Number=2 THEN PartSN ELSE NULL END) Cable_SN2,

    MIN(CASE WHEN Part= 'Pump' AND Number=1 THEN 'Pump' ELSE Null END) [Pump1],
    MIN(CASE WHEN Part= 'Pump' AND Number=1 THEN PartPN ELSE NULL END) Pump_PN1,
    MIN(CASE WHEN Part= 'Pump' AND Number=1 THEN PartSN ELSE NULL END) Pump_SN1,
    MIN(CASE WHEN Part= 'Pump' AND Number=2 THEN 'Pump' ELSE Null END) [Pump2],
    MIN(CASE WHEN Part= 'Pump' AND Number=2 THEN PartPN ELSE NULL END) Pump_PN2,
    MIN(CASE WHEN Part= 'Pump' AND Number=2 THEN PartSN ELSE NULL END) Pump_SN2,

    MIN(CASE WHEN Part= 'MLE' AND Number=1 THEN 'MLE' ELSE NULL END) [MLE1],
    MIN(CASE WHEN Part= 'MLE' AND Number=1 THEN PartPN ELSE NULL END) MLE_PN1,
    MIN(CASE WHEN Part= 'MLE' AND Number=1 THEN PartSN ELSE NULL END) MLE_SN1,
    MIN(CASE WHEN Part= 'MLE' AND Number=2 THEN 'MLE' ELSE NULL END) [MLE2],
    MIN(CASE WHEN Part= 'MLE' AND Number=2 THEN PartPN ELSE NULL END) MLE_PN2,
    MIN(CASE WHEN Part= 'MLE' AND Number=2 THEN PartSN ELSE NULL END) MLE_SN2,

    MIN(CASE WHEN Part= 'AGH' AND Number=1 THEN 'AGH' ELSE NULL END) [AGH1],
    MIN(CASE WHEN Part= 'AGH' AND Number=1 THEN PartPN ELSE NULL END) AGH_PN1,
    MIN(CASE WHEN Part= 'AGH' AND Number=1 THEN PartSN ELSE NULL END) AGH_SN1,
    MIN(CASE WHEN Part= 'AGH' AND Number=2 THEN 'AGH' ELSE NULL END) [AGH2],
    MIN(CASE WHEN Part= 'AGH' AND Number=2 THEN PartPN ELSE NULL END) AGH_PN2,
    MIN(CASE WHEN Part= 'AGH' AND Number=2 THEN PartSN ELSE NULL END) AGH_SN2

FROM CTE
GROUP BY Pull_Date, Well_Name
2

I was wondering whether you want to group by well_name for all different pull date or not. Right now, a well with X dates gets X rows. If you want the X date/rows to become 1, I will update the queries.

  1. Query 1 uses left join
  2. Query 2 uses pivot

SQL Fiddle (Left Join):

;with data as(
    Select Pull_Date, Well_Name, Part, PartPN, Part_SN
        , id = ROW_NUMBER() Over(Partition By Well_Name, Pull_Date, Part Order By Pull_Date)
    From (
        Select Pull_Date, Well_Name, Part1 as Part, Part1_PN as PartPN, Part1_SN Part_SN 
          From test
        Union All
            Select Pull_Date, Well_Name, Part2, Part2_PN, Part2_SN From test
        Union All
            Select Pull_Date, Well_Name, Part3, Part3_PN, Part3_SN From test
        Union All
            Select Pull_Date, Well_Name, Part4, Part4_PN, Part4_SN From test
        Union All
            Select Pull_Date, Well_Name, Part5, Part5_PN, Part5_SN From test
        Union All
            Select Pull_Date, Well_Name, Part6, Part6_PN, Part6_SN From test
    ) as d
), well as (
    Select distinct Pull_Date, Well_Name From test
)
Select w.Pull_Date, w.Well_Name
    , 'BODH1' = b1.Part, 'BODH1_PN' = b1.PartPN, 'BODH1_SN' = b1.Part_SN
    , 'BODH2' = b2.Part, 'BODH2_PN' = b2.PartPN, 'BODH2_SN' = b2.Part_SN
    , 'BODH3' = b3.Part, 'BODH3_PN' = b3.PartPN, 'BODH3_SN' = b3.Part_SN
    , 'BODH4' = b4.Part, 'BODH4_PN' = b4.PartPN, 'BODH4_SN' = b4.Part_SN
    , 'BODH5' = b5.Part, 'BODH5_PN' = b5.PartPN, 'BODH5_SN' = b5.Part_SN
    , 'Pump1' = p1.Part, 'Pump1_PN' = p1.PartPN, 'Pump1_SN' = p1.Part_SN
    , 'Pump2' = p2.Part, 'Pump2_PN' = p2.PartPN, 'Pump2_SN' = p2.Part_SN
    , 'Pump3' = p3.Part, 'Pump3_PN' = p3.PartPN, 'Pump3_SN' = p3.Part_SN
    , 'Pump4' = p4.Part, 'Pump4_PN' = p4.PartPN, 'Pump4_SN' = p4.Part_SN
    , 'Pump5' = p5.Part, 'Pump5_PN' = p5.PartPN, 'Pump5_SN' = p5.Part_SN
    , 'Cable1' = c1.Part, 'Cable1_PN' = c1.PartPN, 'Cable1_SN' = c1.Part_SN
    , 'Cable2' = c2.Part, 'Cable2_PN' = c2.PartPN, 'Cable2_SN' = c2.Part_SN
    , 'Cable3' = c3.Part, 'Cable3_PN' = c3.PartPN, 'Cable3_SN' = c3.Part_SN
    , 'Cable4' = c4.Part, 'Cable4_PN' = c4.PartPN, 'Cable4_SN' = c4.Part_SN
    , 'Cable5' = c5.Part, 'Cable5_PN' = c5.PartPN, 'Cable5_SN' = c5.Part_SN
    , 'AGH1' = a1.Part, 'AGH1_PN' = a1.PartPN, 'AGH1_SN' = a1.Part_SN
    , 'AGH2' = a2.Part, 'AGH2_PN' = a2.PartPN, 'AGH2_SN' = a2.Part_SN
    , 'AGH3' = a3.Part, 'AGH3_PN' = a3.PartPN, 'AGH3_SN' = a3.Part_SN
    , 'AGH4' = a4.Part, 'AGH4_PN' = a4.PartPN, 'AGH4_SN' = a4.Part_SN
    , 'AGH5' = a5.Part, 'AGH5_PN' = a5.PartPN, 'AGH5_SN' = a5.Part_SN
From well w
    Left Join data b1 on b1.Pull_Date = w.Pull_Date and b1.Well_Name = w.Well_Name and b1.Part = 'BODH' and b1.id = 1
    Left Join data b2 on b1.Pull_Date = w.Pull_Date and b2.Well_Name = w.Well_Name and b2.Part = 'BODH' and b2.id = 2
    Left Join data b3 on b1.Pull_Date = w.Pull_Date and b3.Well_Name = w.Well_Name and b3.Part = 'BODH' and b3.id = 3
    Left Join data b4 on b1.Pull_Date = w.Pull_Date and b4.Well_Name = w.Well_Name and b4.Part = 'BODH' and b4.id = 4
    Left Join data b5 on b1.Pull_Date = w.Pull_Date and b5.Well_Name = w.Well_Name and b5.Part = 'BODH' and b5.id = 5
    Left Join data p1 on p1.Pull_Date = w.Pull_Date and p1.Well_Name = w.Well_Name and p1.Part = 'Pump' and p1.id = 1
    Left Join data p2 on p2.Pull_Date = w.Pull_Date and p2.Well_Name = w.Well_Name and p2.Part = 'Pump' and p2.id = 2
    Left Join data p3 on p3.Pull_Date = w.Pull_Date and p3.Well_Name = w.Well_Name and p3.Part = 'Pump' and p3.id = 3
    Left Join data p4 on p4.Pull_Date = w.Pull_Date and p4.Well_Name = w.Well_Name and p4.Part = 'Pump' and p4.id = 4
    Left Join data p5 on p5.Pull_Date = w.Pull_Date and p5.Well_Name = w.Well_Name and p5.Part = 'Pump' and p5.id = 5
    Left Join data c1 on c1.Pull_Date = w.Pull_Date and c1.Well_Name = w.Well_Name and c1.Part = 'Cable' and c1.id = 1
    Left Join data c2 on c2.Pull_Date = w.Pull_Date and c2.Well_Name = w.Well_Name and c2.Part = 'Cable' and c2.id = 2
    Left Join data c3 on c3.Pull_Date = w.Pull_Date and c3.Well_Name = w.Well_Name and c3.Part = 'Cable' and c3.id = 3
    Left Join data c4 on c4.Pull_Date = w.Pull_Date and c4.Well_Name = w.Well_Name and c4.Part = 'Cable' and c4.id = 4
    Left Join data c5 on c5.Pull_Date = w.Pull_Date and c5.Well_Name = w.Well_Name and c5.Part = 'Cable' and c5.id = 5
    Left Join data a1 on a1.Pull_Date = w.Pull_Date and a1.Well_Name = w.Well_Name and a1.Part = 'AGH' and a1.id = 1
    Left Join data a2 on a2.Pull_Date = w.Pull_Date and a2.Well_Name = w.Well_Name and a2.Part = 'AGH' and a2.id = 2
    Left Join data a3 on a3.Pull_Date = w.Pull_Date and a3.Well_Name = w.Well_Name and a3.Part = 'AGH' and a3.id = 3
    Left Join data a4 on a4.Pull_Date = w.Pull_Date and a4.Well_Name = w.Well_Name and a4.Part = 'AGH' and a4.id = 4
    Left Join data a5 on a5.Pull_Date = w.Pull_Date and a5.Well_Name = w.Well_Name and a5.Part = 'AGH' and a5.id = 5
Order By w.Well_Name, w.Pull_Date

Here is another query using a Pivot. It may not be really efficient but I find it is easier to write.

SQL Fiddle (PIVOT):

;with data as(
    Select Pull_Date, Well_Name, Part, Part_PN, Part_SN
        , id = ROW_NUMBER() Over(Partition By Well_Name, Pull_Date, Part Order By Pull_Date)
    From (
        Select Pull_Date, Well_Name, Part1 as Part, Part1_PN as Part_PN, Part1_SN Part_SN 
          From test
        Union All
            Select Pull_Date, Well_Name, Part2, Part2_PN, Part2_SN From test
        Union All
            Select Pull_Date, Well_Name, Part3, Part3_PN, Part3_SN From test
        Union All
            Select Pull_Date, Well_Name, Part4, Part4_PN, Part4_SN From test
        Union All
            Select Pull_Date, Well_Name, Part5, Part5_PN, Part5_SN From test
        Union All
            Select Pull_Date, Well_Name, Part6, Part6_PN, Part6_SN From test
    ) as d
), well as (
    Select Pull_Date, Well_Name, 'Name' = Part+Cast(id as varchar(2)), Part From data
    Union All Select Pull_Date, Well_Name, 'Name' = Part+Cast(id as varchar(2))+'_PN', Part_PN From data
    Union All Select Pull_Date, Well_Name, 'Name' = Part+Cast(id as varchar(2))+'_SN', Part_SN From data
)
Select Pull_Date, Well_Name
    , [BODH1], [BODH1_PN], [BODH1_SN], [BODH2], [BODH2_PN], [BODH2_SN], [BODH3], [BODH3_PN], [BODH3_SN], [BODH4], [BODH4_PN], [BODH4_SN], [BODH5], [BODH5_PN], [BODH5_SN] 
    , [Cable1], [Cable1_PN], [Cable1_SN], [Cable2], [Cable2_PN], [Cable2_SN], [Cable3], [Cable3_PN], [Cable3_SN], [Cable4], [Cable4_PN], [Cable4_SN], [Cable5], [Cable5_PN], [Cable5_SN] 
    , [Pump1], [Pump1_PN], [Pump1_SN], [Pump2], [Pump2_PN], [Pump2_SN], [Pump3], [Pump3_PN], [Pump3_SN], [Pump4], [Pump4_PN], [Pump4_SN], [Pump5], [Pump5_PN], [Pump5_SN] 
    , [AGH1], [AGH1_PN], [AGH1_SN], [AGH2], [AGH2_PN], [AGH2_SN], [AGH3], [AGH3_PN], [AGH3_SN], [AGH4], [AGH4_PN], [AGH4_SN], [AGH5], [AGH5_PN], [AGH5_SN] 
From well
Pivot(
    max(Part)
    For Name in (
        [BODH1], [BODH1_PN], [BODH1_SN], [BODH2], [BODH2_PN], [BODH2_SN], [BODH3], [BODH3_PN], [BODH3_SN], [BODH4], [BODH4_PN], [BODH4_SN], [BODH5], [BODH5_PN], [BODH5_SN] 
        , [Cable1], [Cable1_PN], [Cable1_SN], [Cable2], [Cable2_PN], [Cable2_SN], [Cable3], [Cable3_PN], [Cable3_SN], [Cable4], [Cable4_PN], [Cable4_SN], [Cable5], [Cable5_PN], [Cable5_SN] 
        , [Pump1], [Pump1_PN], [Pump1_SN], [Pump2], [Pump2_PN], [Pump2_SN], [Pump3], [Pump3_PN], [Pump3_SN], [Pump4], [Pump4_PN], [Pump4_SN], [Pump5], [Pump5_PN], [Pump5_SN] 
        , [AGH1], [AGH1_PN], [AGH1_SN], [AGH2], [AGH2_PN], [AGH2_SN], [AGH3], [AGH3_PN], [AGH3_SN], [AGH4], [AGH4_PN], [AGH4_SN], [AGH5], [AGH5_PN], [AGH5_SN] 
    ) 
) as piv
Order By Well_Name, Pull_Date

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.