Pivot not working as expected on SQL Server

Vote count:

0

I am trying to add a 4th pivotted column to my dataset.

Here is the query where I number the records to be pivotted:

SELECT 
        'NEAN' + cast(ROW_NUMBER() over (Partition by GFCUS, ParticipantName order by ParticipantName) as nvarchar(2)) as RNEEAN, 
        'NEIBAN' + cast(ROW_NUMBER() over (Partition by GFCUS, ParticipantName order by  ParticipantName) as nvarchar(2)) as RNUM, 
        'SCSAC' +  cast(ROW_NUMBER() over (Partition by GFCUS, ParticipantName order by ParticipantName) as nvarchar(2)) as RNTYPE,
        'APPRODUCT' +  cast(ROW_NUMBER() over (Partition by GFCUS, ParticipantName order by ParticipantName) as nvarchar(2)) as RNPRODUCT,  
        GFCUS, NEEAN, NEIBAN, SCSAC, ParticipantName, ParticipantSalutation, SVCSA,  GFCOD, Product, SCAI30, SCAI97,
        MAX(GFCTP) GFCTP,MAX(GFCUN) GFCUN,
        MAX(BGCFN1) BGCFN1, MAX(BGCFN2) BGCFN2, MAX(BGCFN3) BGCFN3,     
        MAX(SVNA1) SVNA1, MAX(SVNA2) SVNA2, MAX(SVNA3) SVNA3, MAX(SVNA4) SVNA4, 
        MAX(SVNA5) SVNA5, MAX(SVPZIP) SVPZIP, NONUK, Flag_Salu, Flag_Name, Flag_Addr
into #tmpAHSBC
FROM ##ExtrctA 
GROUP BY   GFCUS, ParticipantName, ParticipantSalutation, SVCSA, SCSAC, GFCOD, Product, SCAI30, SCAI97,
    NEEAN, NEIBAN, NONUK, Flag_Salu, Flag_Name, Flag_Addr
ORDER BY GFCUS, RNUM, RNTYPE

this query works fine.

I now need to pivot the four numbered columns, and although the first 3 work fine, the fourth Product is showing as NULL, even though there is data.

select GFCUS,ParticipantName,ParticipantSalutation, MAX(GFCTP) GFCTP,
        MAX(NEAN1) [NEAN1], MAX([NEAN2]) [NEAN2],MAX([NEAN3]) [NEAN3],
        MAX([NEIBAN1]) [NEIBAN1],MAX([NEIBAN2]) [NEIBAN2],MAX([NEIBAN3]) [NEIBAN3],
        Max([SCSAC1]) [SCSAC1], Max([SCSAC2]) [SCSAC2], Max([SCSAC3]) [SCSAC3], 
        Max(APPROD1) [APPROD1], Max([APPROD2]) [APPROD2], Max([APPROD3]) [APPROD3], 
        GFCOD, SCAI30, SCAI97,
         SVCSA,NONUK, Flag_Salu, Flag_Name, Flag_Addr
into #final
from #tmpAHSBC HS 

PIVOT(
    min(NEEAN) for RNEEAN IN (NEAN1, NEAN2, NEAN3) -- External Account Number
) p1

PIVOT(
    min(NEIBAN) for RNUM in ([NEIBAN1],[NEIBAN2],[NEIBAN3]) -- IBAN
) p2

PIVOT(
    min(SCSAC) for RNTYPE in ([SCSAC1], [SCSAC2], [SCSAC3]) -- Account Type
) p3

PIVOT(
    min(PRODUCT) for RNPRODUCT in (APPROD1, APPROD2, APPROD3) -- Product Name
) p4
GROUP BY GFCUS,ParticipantName,ParticipantSalutation, SVCSA, GFCOD, SCAI30, SCAI97,NONUK, SVCSA,NONUK,  Flag_Salu, Flag_Name, Flag_Addr

what have I missed please?

asked 41 secs ago

Philip

2,462

Pivot not working as expected on SQL Server