How to pivot two columns or do it another way.

  • Tech

    SSC Veteran

    Points: 274

    The two main tables are EquipWorkOrder and EquipWorkOrderHrs. I have two columns that I need to pivot in the EquipWorkOrderHrs table when I join the two togeather by EquipWorkOrderID. The other joins are to give info about the equipment and more Info about the work order.   Based on the work order you can have only 1 tech or as many as 5 techs. What I have below gives me the current history of the data in the table. In the long run I need to pivot those two columns based on EquipWorkOrderID.

    example data

    what I get with the current query, I left some columns off

    `

    EquipWorkOrderID/DateTm/Equip/AccountCode/Descr/Description/Resolution/Status/UserNm/Hrs/and so on

    1 / 08/26/2020 08:46:16 /Gator/01234/John Deere Gator/ Test/Test/ Completed/ John / 1/ ...

    1/ 08/26/2020 08:46:16 /Gator/01234/John Deere Gator/ Test/Test/ Completed/ Mike / 1/ ..

    1/ 08/26/2020 08:46:16 /Gator/01234/John Deere Gator/ Test/Test/ Completed/ Billy / 1/...

    1/ 08/26/2020 08:46:16 /Gator/01234/John Deere Gator/ Test/Test/ Completed/ Aaron/ 1/ ...

    1/ 08/26/2020 08:46:16 /Gator/01235/John Deere Loader/ Test/Test/ Completed/ Jason/ 2/ ...

    2/ 08/26/2020 08:50:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Mark/ 2/ ...

    2/ 08/26/2020 08:50:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Trent/ 2/ ...

    3/ 08/26/2020 08:52:00 /Welder/01236/Miller Welder/ Test/Test/ Completed/ John/ 2/ ...

    4 08/26/2020 08:53:00 /Skidsteer/01236/John Deere Skidsteer/ Test/Test/ Completed/ Mike/ 3/ ...

    4 08/26/2020 08:53:00 /Skidsteer/01236/John Deere Skidsteer/ Test/Test/ Completed/ Tony/3/ ...

    5 08/26/2020 08:54:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Tim  2/ ...

    5 08/26/2020 08:54:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Joe  2/ ...

    5 08/26/2020 08:54:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Jeff  2/ ...

    5 08/26/2020 08:54:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Mike  2/ ...

    `

    What I would like to see...

    `

    EquipWorkOrderID/DateTm/Equip/AccountCode/Descr/Description/Resolution/Status/UserNm1/Hrs1/UserNm2/Hrs2/UserNm3/Hrs3/UserNm4/Hrs4/UserNm5/Hrs5/and so on

    1 / 08/26/2020 08:46:16 /Gator/01234/John Deere Gator/ Test/Test/ Completed/ John / 1/Mike/1/Billy/1/Aaron/1/Jason/1/

    2/ 08/26/2020 08:50:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Mark/ 2/ Trent/2/

    3/ 08/26/2020 08:52:00 /Welder/01236/Miller Welder/ Test/Test/ Completed/ John/ 2/

    4 08/26/2020 08:53:00 /Skidsteer/01235/John Deere Skidsteer/ Test/Test/ Completed/ Mike/ 3/ Tony/ 3/...

    5 08/26/2020 08:54:00 /Loader/01235/John Deere Loader/ Test/Test/ Completed/ Tim  / 5/ Joe/5/Jeff/5/Mike/5/...

    `

     

    `

    SELECT Top 1000 a.EquipWorkOrderID, a.DateTm, f.Equip, f.AccountCode, f.Descr,a.Description, a.Resolution, g.Status, c.UserNm, b.Hrs, d.Category, i.MaintLoc, j.EquipType, e.Crew, a.MeterReading,k.Type, a.Comments

    FROM EquipWorkOrder a

    Left Join EquipWorkOrderHrs b

    On a.EquipWorkOrderID = b.EquipWorkOrderID

    Left Join AppUser c

    On c.UserID = b.UserID

    Left Join Category d

    On a.CategoryID = d.CategoryID

    Left Join Crew e

    On e.CrewID = a.CrewID

    Left Join Equipment f

    On a.EquipmentID = f.EquipmentID

    Left Join Status g

    On a.StatusID = g.StatusID

    Left Join PlantLoc h

    On a.PlantLocID = h.PlantLocID

    Left Join MaintLocation i

    On a.MaintLocationID = i.MaintLocationID

    Left Join EquipType j

    On j.EquipTypeID = a.EquipTypeID

    Left Join Type k

    On f.TypeID = k.TypeID

    order by a.DateTm Desc

    `

  • pietlinden

    SSC Guru

    Points: 62902

    Got some sample records?  And how about a couple of CREATE TABLE scripts. Or do you have something against tested queries?

  • Tech

    SSC Veteran

    Points: 274

    I am working in Ignition. I do not have access to create statements or sample records.

    Here is what I come up but I removed some of the joins until I get something figured out. This pivots the table but it puts 0's in for the hrs values. I all can't see the username (UserNm).

    `

    Select * From

    (

    Select

    a.EquipWorkOrderID,

    c.UserNm,

    b.Hrs

    From

    EquipWorkOrder a

    Left Join EquipWorkOrderHrs b

    On a.EquipWorkOrderID = b.EquipWorkOrderID

    Left Join AppUser c

    On c.UserID = b.UserID

    ) t

    Pivot (

    Count(Hrs)

    For UserNm IN (

    [Tech1],

    [Tech2],

    [Tech3],

    [Tech4],

    [Tech5])

    ) AS pivot_table

     

    `

    • This reply was modified 1 month, 2 weeks ago by  Tech.
  • pietlinden

    SSC Guru

    Points: 62902

    I created a table for this... (Far easier to do in SSRS with a matrix visual)

    now someone who's better at Pivot than I am can have a play.

    --EquipWorkOrderID/DateTm/Equip/AccountCode/Descr/Description/Resolution/Status/UserNm/Hrs
    use tempdb;
    go
    CREATE TABLE WorkOrder (
    EquipWorkOrderID INT
    ,DateTm DATETIME
    ,Equip VARCHAR(10)
    ,AccountCode INT
    ,Descr VARCHAR(20)
    ,[Description] VARCHAR(10)
    ,Resolution VARCHAR(10)
    ,OrderStatus VARCHAR(10)
    ,UserNm VARCHAR(8)
    ,Hrs INT);
    GO

    INSERT INTO WorkOrder VALUES
    (1 ,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' John ', 1)
    ,(1,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' Mike ', 1)
    ,(1,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' Billy ', 1)
    ,(1,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' Aaron', 1)
    ,(1,'08/26/2020 08:46:16', 'Gator', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Jason',2)
    ,(2,'08/26/2020 08:50:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Mark', 2)
    ,(2,'08/26/2020 08:50:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Trent', 2)
    ,(3,'08/26/2020 08:52:00', 'Welder', '01236', 'Miller Welder', ' Test', 'Test', ' Completed', ' John', 2)
    ,(2,'08/26/2020 08:53:00', 'Skidsteer', '01236', 'John Deere Skidsteer', ' Test', 'Test', ' Completed', ' Mike', 2)
    ,(4, '08/26/2020 08:53:00', 'Skidsteer', '01236', 'John Deere Skidsteer', ' Test', 'Test', ' Completed', ' Tony', 2)
    ,(5, '08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Tim', 2)
    ,(5, '08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Joe', 2)
    ,(5, '08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Jeff', 2)
    ,(5, '08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Mike', 2);

  • Jeffrey Williams

    SSC Guru

    Points: 88696

    I think this should get you started (thanks to pietlinden for the sample data):

    --EquipWorkOrderID/DateTm/Equip/AccountCode/Descr/Description/Resolution/Status/UserNm/Hrs
    Declare @WorkOrder Table (
    EquipWorkOrderID INT
    , DateTm DATETIME
    , Equip VARCHAR(10)
    , AccountCode char(5)
    , Descr VARCHAR(20)
    , [Description] VARCHAR(10)
    , Resolution VARCHAR(10)
    , OrderStatus VARCHAR(10)
    , UserNm VARCHAR(8)
    , Hrs INT);

    Insert Into @WorkOrder
    Values (1 ,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' John ', 1)
    , (1 ,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' Mike ', 1)
    , (1 ,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' Billy ', 1)
    , (1 ,'08/26/2020 08:46:16', 'Gator', '01234', 'John Deere Gator', ' Test', 'Test', ' Completed', ' Aaron', 1)
    , (1 ,'08/26/2020 08:46:16', 'Gator', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Jason',2)
    , (2 ,'08/26/2020 08:50:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Mark', 2)
    , (2 ,'08/26/2020 08:50:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Trent', 2)
    , (3 ,'08/26/2020 08:52:00', 'Welder', '01236', 'Miller Welder', ' Test', 'Test', ' Completed', ' John', 2)
    , (2 ,'08/26/2020 08:53:00', 'Skidsteer', '01236', 'John Deere Skidsteer', ' Test', 'Test', ' Completed', ' Mike', 2)
    , (4 ,'08/26/2020 08:53:00', 'Skidsteer', '01236', 'John Deere Skidsteer', ' Test', 'Test', ' Completed', ' Tony', 2)
    , (5 ,'08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Tim', 2)
    , (5 ,'08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Joe', 2)
    , (5 ,'08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Jeff', 2)
    , (5 ,'08/26/2020 08:54:00', 'Loader', '01235', 'John Deere Loader', ' Test', 'Test', ' Completed', ' Mike', 2);

    With userRows
    As (
    Select *
    , UserRowNum = row_number() over(Partition By wo.EquipWorkOrderID Order By wo.UserNm)
    From @WorkOrder wo
    )
    Select usr.EquipWorkOrderID
    , DateTm = max(usr.DateTm)
    , Equip = max(usr.Equip)
    , AccountCode = min(usr.AccountCode)
    , Descr = max(usr.Descr)
    , [Description] = max(usr.[Description])
    , Resolution = max(usr.Resolution)
    , OrderStatus = max(usr.OrderStatus)
    , UserName1 = max(iif(usr.UserRowNum = 1, usr.UserNm, Null))
    , UserHours1 = max(iif(usr.UserRowNum = 1, usr.Hrs, Null))
    , UserName2 = max(iif(usr.UserRowNum = 2, usr.UserNm, Null))
    , UserHours2 = max(iif(usr.UserRowNum = 2, usr.Hrs, Null))
    , UserName3 = max(iif(usr.UserRowNum = 3, usr.UserNm, Null))
    , UserHours3 = max(iif(usr.UserRowNum = 3, usr.Hrs, Null))
    , UserName4 = max(iif(usr.UserRowNum = 4, usr.UserNm, Null))
    , UserHours4 = max(iif(usr.UserRowNum = 4, usr.Hrs, Null))
    , UserName5 = max(iif(usr.UserRowNum = 5, usr.UserNm, Null))
    , UserHours5 = max(iif(usr.UserRowNum = 5, usr.Hrs, Null))
    From userRows usr
    Group By
    usr.EquipWorkOrderID;

    You may need to adjust the row_number() function - there are some differences in the data that you may need to account for...for example, if the date/time is actually different for each user - or there is something else that can be used to order the rows appropriately.

     

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Tech

    SSC Veteran

    Points: 274

    I get this error when I run the query. (Must declare the table variable "@WorkOrder".)

  • Jeffrey Williams

    SSC Guru

    Points: 88696

    Did you try running my example - or just the query?  You need to change the example to fit your tables.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff Moden

    SSC Guru

    Points: 997308

    Tech,

    Is there a fixed maximum number of people that can work on a work order?

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 997308

    Never mind...

    Jeff Williams is using the result of your query... that makes the CROSSTAB (which does the pivot) a bit more complicated and, perhaps, a bit slower (maybe not... it's just been my experience that it can).

    With that, I suggest the following.  There are some notes in the comments that you should read.  This replaces your original query, as well.

       WITH 
    ctePreAgg AS
    (--==== Determine the display order for the CROSSTAB (pivot).
    -- This can also speed things up a bit if there's multiple UserID rows for the same EquipWorkOrderID
    SELECT b.EquipWorkOrderID
    ,c.UserNm
    ,Hrs = SUM(b.Hrs)
    ,DisplayOrder = ROW_NUMBER() OVER (PARTITION BY b.EquipWorkOrderID ORDER BY c.UserNm)
    FROM #EquipWorkOrderHrs b
    JOIN #AppUser c
    ON b.UserID = c.UserID
    GROUP BY b.EquipWorkOrderID,c.UserNm
    )
    ,cteCrossTab AS
    (--==== This does the CROSSTAB (pivot). If you need more that 5 double slots, we'll need to go "dynamic"
    SELECT EquipWorkOrderID
    ,UserNm1 = MAX(CASE WHEN DisplayOrder = 1 THEN UserNm ELSE '' END)
    ,Hrs1 = MAX(CASE WHEN DisplayOrder = 1 THEN CONVERT(VARCHAR(10),Hrs) ELSE '' END)
    ,UserNm2 = MAX(CASE WHEN DisplayOrder = 2 THEN UserNm ELSE '' END)
    ,Hrs2 = MAX(CASE WHEN DisplayOrder = 2 THEN CONVERT(VARCHAR(10),Hrs) ELSE '' END)
    ,UserNm3 = MAX(CASE WHEN DisplayOrder = 3 THEN UserNm ELSE '' END)
    ,Hrs3 = MAX(CASE WHEN DisplayOrder = 3 THEN CONVERT(VARCHAR(10),Hrs) ELSE '' END)
    ,UserNm4 = MAX(CASE WHEN DisplayOrder = 4 THEN UserNm ELSE '' END)
    ,Hrs4 = MAX(CASE WHEN DisplayOrder = 4 THEN CONVERT(VARCHAR(10),Hrs) ELSE '' END)
    ,UserNm5 = MAX(CASE WHEN DisplayOrder = 5 THEN UserNm ELSE '' END)
    ,Hrs5 = MAX(CASE WHEN DisplayOrder = 5 THEN CONVERT(VARCHAR(10),Hrs) ELSE '' END)
    ,TotalHrs
    FROM ctePreAgg
    GROUP BY EquipWorkOrderID
    )--==== NOW we can add all the other stuff.
    SELECT p.EquipWorkOrderID --Note the alias change
    ,a.DateTm
    ,f.Equip
    ,f.AccountCode
    ,f.Descr
    ,a.Description
    ,a.Resolution
    ,g.STATUS
    ,p.UserNm --Note the alias change
    ,p.Hrs --Note the alias change
    ,d.Category
    ,i.MaintLoc
    ,j.EquipType
    ,e.Crew
    ,a.MeterReading
    ,k.Type
    ,a.Comments
    ,p.UserNm1, p.Hrs1
    ,p.UserNm2, p.Hrs2
    ,p.UserNm3, p.Hrs3
    ,p.UserNm4, p.Hrs4
    ,p.UserNm5, p.Hrs5
    FROM cteCrossTab p
    LEFT JOIN EquipWorkOrder a ON p.EquipWorkOrderID = a.EquipWorkOrderID
    LEFT JOIN EquipWorkOrderHrs b ON b.EquipWorkOrderID = a.EquipWorkOrderID
    --LEFT JOIN AppUser c ON b.UserID = c.UserID --NOT NEEDED ANYMORE
    LEFT JOIN Category d ON d.CategoryID = a.CategoryID
    LEFT JOIN Crew e ON e.CrewID = a.CrewID
    LEFT JOIN Equipment f ON f.EquipmentID = a.EquipmentID
    LEFT JOIN STATUS g ON g.StatusID = a.StatusID
    LEFT JOIN PlantLoc h ON h.PlantLocID = a.PlantLocID
    LEFT JOIN MaintLocation i ON i.MaintLocationID = a.MaintLocationID
    LEFT JOIN EquipType j ON j.EquipTypeID = a.EquipTypeID
    LEFT JOIN Type k ON k.TypeID = f.TypeID
    ORDER BY a.DateTm DESC
    ;

    For more information on CROSSTABs and Dynamic CROSSTABs and for reasons why neither Mr. Williams or I used the god awful PIVOT operator, please read the articles at the following links...

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%e2%80%93-converting-rows-to-columns-1

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply