SQL equivalent when PIVOT is not available in SQL Server CE

Question

I have a problem with a SQL query that runs correctly except on Microsoft SQL Server CE (no PIVOT support). The query is the following:

SELECT 
    *, [1] as IMGN1, [2] as IMGN2, [3] as IMGN3,
    [4] as IMGN4, [5] as IMGN5, [6] as IMGN6,
    [7] as IMGN7, [8] as IMGN8, [9] as IMGN9, 
    [10] as IMGN10 
FROM
    (SELECT 
         area.CoilId as CID, area.DEFECTID,
         (SELECT SUM(s2.endposmd - s2.startposmd) 
          FROM sections s2 
          WHERE s2.OutCoilID = 999999 
            AND s2.InCoilId <= area.coilid) AS POSITIONMD, 
         d1.DNO as CAMERADEFECTNO, d1.IMAGE_NO as IMAGE_NO,
         area.MERGEDTO as MERGEDTO
     FROM 
         (OutCoils AS oc
     INNER JOIN 
         sections AS s ON oc.OutCoilId = s.OutCoilId
     INNER JOIN 
         defects AS area ON area.coilid = s.InCoilId  
                         AND area.PositionMD >= s.StartPosMD 
                         AND area.PositionMD <= s.EndPosMD
     INNER JOIN 
         defects AS d1 ON d1.CoilId = area.CoilId 
                       AND d1.MergedTo = area.DEFECTID)   
     WHERE 
         oc.OutCoilID = 999999 AND area.MergedTo = -2) AS SourceTable 
PIVOT
    (MIN([CAMERADEFECTNO]) FOR [IMAGE_NO]
          IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
    ) AS PivotTable 
ORDER BY 
    PositionMD;

How can this be translated to a valid SQL query for non PIVOT editions?

I tried something using CASE but I have a problem with the subquery inside the aggregate function I use to get POSITIONMD, the rest is already working properly. Any idea of how to get POSITIONMD?

SELECT 
area.DEFECTID as DEFECTID, 
min(CASE when d1.MERGEDTO = area.DEFECTID then area.COILID end) CID,
min(CASE when d1.MERGEDTO = area.DEFECTID then area.MERGEDTO end) MERGEDTO,
min(CASE when d1.MERGEDTO = area.DEFECTID then (select sum(s2.endposmd - s2.startposmd) from sections s2 where s2.OutCoilID=999999 and s2.InCoilId<=area.coilid) end) POSITIONMD,
sum(CASE when d1.IMAGE_NO = 1 then (d1.DNO) end) IMGN1,
sum(CASE when d1.IMAGE_NO = 2 then (d1.DNO) end) IMGN2,
sum(CASE when d1.IMAGE_NO = 3 then (d1.DNO) end) IMGN3,
sum(CASE when d1.IMAGE_NO = 4 then (d1.DNO) end) IMGN4,
sum(CASE when d1.IMAGE_NO = 5 then (d1.DNO) end) IMGN5,
sum(CASE when d1.IMAGE_NO = 6 then (d1.DNO) end) IMGN6,
sum(CASE when d1.IMAGE_NO = 7 then (d1.DNO) end) IMGN7,
sum(CASE when d1.IMAGE_NO = 8 then (d1.DNO) end) IMGN8,
sum(CASE when d1.IMAGE_NO = 9 then (d1.DNO) end) IMGN9,
sum(CASE when d1.IMAGE_NO = 10 then (d1.DNO) end) IMGN10
FROM ( steinb.OutCoils AS oc
INNER JOIN steinb.sections AS s ON oc.OutCoilId=s.OutCoilId
INNER JOIN steinb.defects AS area ON area.coilid=s.InCoilId  AND area.PositionMD>=s.StartPosMD AND area.PositionMD<=s.EndPosMD
INNER JOIN steinb.defects AS d1 ON d1.CoilId=area.CoilId AND d1.MergedTo=area.DEFECTID AND d1.IMAGE_NO!=0)  
WHERE oc.OutCoilID=999999 GROUP BY area.DEFECTID ORDER BY PositionMD;

Thank you a lot.

Answer 1

Gotcha. I believe you just need to remove the MIN:

SELECT 
    DEFECTID = area.DEFECTID
    , CID = MIN(CASE when d1.MERGEDTO = area.DEFECTID then area.COILID end) 
    , MERGEDTO = MIN(CASE when d1.MERGEDTO = area.DEFECTID then area.MERGEDTO end) 
    , POSITIONMD = CASE when d1.MERGEDTO = area.DEFECTID then (select sum(s2.endposmd - s2.startposmd) from sections s2 where s2.OutCoilID=999999 and s2.InCoilId<=area.coilid) END
    , IMGN1 = sum(CASE when d1.IMAGE_NO = 1 then (d1.DNO) END)
    , IMGN2 = sum(CASE when d1.IMAGE_NO = 2 then (d1.DNO) end)
    , IMGN3 = sum(CASE when d1.IMAGE_NO = 3 then (d1.DNO) end)
    , IMGN4 = sum(CASE when d1.IMAGE_NO = 4 then (d1.DNO) end)
    , IMGN5 = sum(CASE when d1.IMAGE_NO = 5 then (d1.DNO) end)
    , IMGN6 = sum(CASE when d1.IMAGE_NO = 6 then (d1.DNO) end)
    , IMGN7 = sum(CASE when d1.IMAGE_NO = 7 then (d1.DNO) end)
    , IMGN8 = sum(CASE when d1.IMAGE_NO = 8 then (d1.DNO) end)
    , IMGN9 = sum(CASE when d1.IMAGE_NO = 9 then (d1.DNO) end)
    , IMGN10 = sum(CASE when d1.IMAGE_NO = 10 then (d1.DNO) end)
FROM ( steinb.OutCoils AS oc
    INNER JOIN steinb.sections AS s ON oc.OutCoilId=s.OutCoilId
    INNER JOIN steinb.defects AS area ON area.coilid=s.InCoilId  AND area.PositionMD>=s.StartPosMD AND area.PositionMD<=s.EndPosMD
    INNER JOIN steinb.defects AS d1 ON d1.CoilId=area.CoilId AND d1.MergedTo=area.DEFECTID AND d1.IMAGE_NO!=0)  
WHERE oc.OutCoilID=999999 GROUP BY area.DEFECTID ORDER BY PositionMD;