Is there a general relationship between left outer join and PIVOT?

Question

I just got my head around left outer joins, and I am practicing PIVOTS.

I am wondering whether it is possible to convert this query into a pivot?

SELECT mydrawercustomer.customerface, 
       mydrawerordertype.ordertypeface, 
       mydrawerorder.LM, 
       Datediff(hour, a1.playdate, a2.playdate) AS [RunningTime1], 
       Datediff(hour, a2.playdate, a3.playdate) AS [RunningTime2], 
       Datediff(hour, a3.playdate, a4.playdate) AS [RunningTime3], 
       Datediff(hour, a1.playdate, a4.playdate) AS [RunningTime4] 
FROM   mydrawerorder 
       LEFT OUTER JOIN mydrawercustomer 
                    ON mydrawerorder.customerKEY = mydrawercustomer.customerKEY 
       LEFT OUTER JOIN mydrawerorderactivity a1 
                    ON mydrawerorder.orderKEY = a1.orderKEY 
                       AND a1.activityKEY = 1 
       LEFT OUTER JOIN mydrawerorderactivity a2 
                    ON mydrawerorder.orderKEY = a2.orderKEY 
                       AND a2.activityKEY = 2 
       LEFT OUTER JOIN mydrawerorderactivity a3 
                    ON mydrawerorder.orderKEY = a3.orderKEY 
                       AND a3.activityKEY = 3 
       LEFT OUTER JOIN mydrawerorderactivity a4 
                    ON mydrawerorder.orderKEY = a4.orderKEY 
                       AND a4.activityKEY = 4 
       INNER JOIN mydrawerordertype 
               ON mydrawerorder.ordertypeKEY = mydrawerordertype.ordertypeKEY 
ORDER  BY mydrawercustomer.customerface, 
          mydrawerordertype.ordertypeface, 
          mydrawerorder.LM, 
          a1.playdate

I'm not requesting you to solve my challenge for me, but I am just requesting help on getting started on converting this type of left outer join query into a PIVOT.

Hi, give a look to this link it should help to figure out how to convert the query http://www.techbubbles.com/sql-server/pivot-unpivot-operators-in-sql-server-2008/ — Raphael, Nov 04 '13 at 07:04
In this case more useful link will be the following: http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — Sandr, Nov 04 '13 at 07:35
Could you give an example of the data, please?(It's possible on the sqlfiddle) — Aleksandr Fedorenko, Nov 04 '13 at 09:58
The multiple join version [might be more efficient](http://stackoverflow.com/questions/7448453/sql-server-pivot-vs-multiple-join/7449213#7449213) depending on indexes available and your data, — Martin Smith, Nov 04 '13 at 12:14

score 3 · Accepted Answer · answered Nov 04 '13 at 07:28

Usually the relation between LEFT JOIN and PIVOT is not straight forward, but in your case the solution can be found.

Your query will looks as following:

select customerface, ordertypeface, LM,
    Datediff(hour, [1], [2]) AS [RunningTime1],
    Datediff(hour, [2], [3]) AS [RunningTime2],
    Datediff(hour, [3], [4]) AS [RunningTime3],
    Datediff(hour, [1], [4]) AS [RunningTime4]
from
(
    SELECT
        c.customerface, 
        ot.ordertypeface, 
        o.LM,
        a.activityKEY,
        a.playdate,
        o.orderKEY
    FROM mydrawerorder o
    INNER JOIN mydrawerordertype ot
        ON o.ordertypeKEY = ot.ordertypeKEY 
    LEFT JOIN mydrawercustomer c
        ON o.customerKEY = c.customerKEY 
    LEFT JOIN mydrawerorderactivity a
        ON o.orderKEY = a.orderKEY 
        and a.activityKEY in (1, 2, 3, 4)
) x
pivot
(
    max(playdate)
    for activityKEY in ([1], [2], [3], [4])
) as pvt
ORDER  BY customerface, ordertypeface, LM, [1]

Additional information according PIVOT using can be found here: http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Is there a general relationship between left outer join and PIVOT?

1 Answers1