How to use a Left Join with a bunch of other joins

Question

I am trying to join two tables based on EquipWorkOrderID. Tables(EquipWorkOrder and EquipWorkOrderHrs) With the query I have below it duplicates the row based on ID if there is two UserNm's for the Same ID. I want the two UserNm's and the Hrs if the ID match's in the same role if possible.

example of what my results give me now

EquipWorkOrderID/Equip/Description/Resolution/UserNm/Hrs
---------------------------------------------------------
1              / ForkLift / Bad   /Fixed/John Doe / 2
1              /Forklift / Bad    /Fixed/Jane Doe /2

What I would Like to see

EquipWorkOrderID/Equip/Description/Resolution/UserNm1/Hrs1/UserNm2/Hrs2
---------------------------------------------------------
1              / ForkLift / Bad   /Fixed/John Doe  / 2  / Jane Doe / 2

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
            

Answer 1

I have placed the result of your query in a table (selection) and retrieved the data from it in a common table expression (cte). Replace the content of the CTE with your query and add the two new columns I created (UsrNum and HrsNum).

My solution uses a double pivot (one for the UserNm column and one for the Hrs column) followed by a grouping. This may not be ideal, but it gets the job done.

Here is a fiddle to show how I built up the solution.

Sample data

This just recreates the results of your current query.

create table selection
(
  EquipWorkOrderID int,
  Equip nvarchar(10),
  Description nvarchar(10),
  Resolution nvarchar(10),
  UserNm nvarchar(10),
  Hrs int
);

insert into selection (EquipWorkOrderID,Equip,Description,Resolution,UserNm,Hrs) values
(1, 'ForkLift', 'Bad', 'Fixed', 'John Doe', 2),
(1, 'Forklift', 'Bad', 'Fixed', 'Jane Doe', 2);

Solution

Replace the first part of the CTE with your query and add the two new columns.

with cte as
(
  select EquipWorkOrderID,Equip,Description,Resolution,UserNm,Hrs,
   'Usr' + convert(nvarchar(10),row_number() over(partition by Equip order by UserNm)) as 'UsrNum',
   'Hrs' + convert(nvarchar(10),row_number() over(partition by Equip order by UserNm)) as 'HrsNum'
  from selection
)
select ph.EquipWorkOrderId, ph.Equip, ph.Description, ph.Resolution,
       max(ph.Usr1) as 'UserNm1',
       max(ph.Hrs1) as 'Hrs1',
       max(ph.Usr2) as 'UserNm2',
       max(ph.Hrs2) as 'Hrs2'
from cte c
pivot (max(c.UserNm) for c.UsrNum in ([Usr1], [Usr2])) pu
pivot (max(pu.Hrs) for pu.HrsNum in ([Hrs1], [Hrs2])) ph
group by ph.EquipWorkOrderId, ph.Equip, ph.Description, ph.Resolution;

Result

Outcome looks like this. Jane Doe is UserNm1 because that is how the new UserNum column was constructed (order by UserNm). Adjust the order by if you need John Doe to remain first.

EquipWorkOrderId  Equip     Description  Resolution  UserNm1   Hrs1  UserNm2  Hrs2
----------------- --------- ------------ ----------- --------- ----- -------- -----
1                 ForkLift  Bad          Fixed       Jane Doe  2     John Doe 2

---

Edit: solution merged with original query (untested)

with cte as
(
    SELECT TOP 1000
        --Start original selection field list
        ewo.EquipWorkOrderID,
        ewo.DateTm,
        equ.Equip,
        equ.AccountCode,
        equ.Descr,
        ewo.Description,
        ewo.Resolution,
        sta.Status,
        au.UserNm,
        ewoh.Hrs,
        cat.Category,
        ml.MaintLoc,
        equt.EquipType,
        cre.Crew,
        ewo.MeterReading,
        typ.Type,
        --Added two new fields
        'Usr' + convert(nvarchar(10),row_number() over(partition by Equip order by UserNm)) as 'UsrNum',
        'Hrs' + convert(nvarchar(10),row_number() over(partition by Equip order by UserNm)) as 'HrsNum'
    FROM EquipWorkOrder ewo
    JOIN EquipWorkOrderHrs ewoh
        ON ewo.EquipWorkOrderID = ewoh.EquipWorkOrderID
    JOIN AppUser au
        ON au.UserID = ewoh.UserID
    JOIN Category cat
        ON cat.CategoryID = ewo.CategoryID
    JOIN Crew cre
        ON cre.CrewID = ewo.CrewID
    JOIN Equipment equ
        ON equ.EquipmentID = ewo.EquipmentID
    JOIN Status sta
        ON sta.StatusID = ewo.StatusID
    JOIN PlantLoc pll
        ON pll.PlantLocID = ewo.PlantLocID
    JOIN MaintLocation ml
        ON ml.MaintLocationID = ewo.MaintLocationID
    JOIN EquipType equt
        ON equt.EquipTypeID = ewo.EquipTypeID
    JOIN Type typ
        ON typ.TypeID = equ.TypeID
    ORDER BY ewo.DateTm DESC
)
select ph.EquipWorkOrderId, ph.Equip, ph.Description, ph.Resolution,
       max(ph.Usr1) as 'UserNm1',
       max(ph.Hrs1) as 'Hrs1',
       max(ph.Usr2) as 'UserNm2',
       max(ph.Hrs2) as 'Hrs2'
from cte c
pivot (max(c.UserNm) for c.UsrNum in ([Usr1], [Usr2])) pu
pivot (max(pu.Hrs) for pu.HrsNum in ([Hrs1], [Hrs2])) ph
group by ph.EquipWorkOrderId, ph.Equip, ph.Description, ph.Resolution;

Edit2: how to use pivot...

Select pivot_table.*
From
(
    Select  a.EquipWorkOrderID,
            b.Hrs,
            c.UserNm,
            'Tech' + convert(nvarchar(10), row_number() over(order by c.UserNm)) -- construct _generic_ names
    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 -- UserNm does not contain values like "Tech1" or "Tech2"
*/
Pivot (Count(Hrs) For GenUserNm  IN ([Tech1], [Tech2], [Tech3], [Tech4], [Tech5])) AS pivot_table -- pivot over the _generic_ names