Pivot a result set

Question

I am attempting to take a result set which contains rows of data that have a GUID, a Percentage value, a name and a bit flag and pivot that data so it is grouped by the GUID and has a set of columns for the remaining fields. Each GUID can have up to five rows of data in the original result set but may have none or a number between one and five.

The desired output should look like the following in terms of columns:

GUID, Name1, Perc1, Flag1, Name2, Perc2, Flag2, Name3, Perc3, Flag3, Name4, Perc4, Flag4, Name5, Perc5, Flag5

I have been able to PIVOT the data so that I can get the Names to appear against the GUID but I'm not sure of how to extend this to get the other fields (The Perc and the Flag) for each name into the result set.

SELECT Id, Insurer1, Insurer2, Insurer3, Insurer4, Isurer5
FROM (
   SELECT Policy.Id,
      BinderInsurer.[Percentage] As BinderInsurerPerc,
      BinderInsurer.LeadInsurer,
      Account.RegisteredCompanyName As BinderInsurerName,
      ROW_NUMBER() OVER (PARTITION BY Policy.Id ORDER BY BinderInsurer.[Percentage] DESC) AS PolCount,
      'Insurer' + CONVERT(VARCHAR(6), ROW_NUMBER() OVER (PARTITION BY Policy.Id ORDER BY BinderInsurer.[Percentage] DESC)) AS PivotKey
   FROM [Policy]
        Left Join Scheme On Scheme.Id = [Policy].SchemeId
    Left Join BinderAgreement On BinderAgreement.Id = Scheme.BinderAgreementId
    Left Join BinderSection On BinderSection.Id = Scheme.BinderSectionId
    Left Join BinderPeriod On BinderPeriod.Id = Scheme.BinderPeriodId
    Left Join BinderInsurer On BinderAgreement.Id = BinderInsurer.BinderAgreementId
    Left Join Account On Account.Id = BinderInsurer.AccountId
    ) a
PIVOT
(
MAX(a.BinderInsurerName)
FOR PivotKey IN ([Insurer1], [Insurer2], [Insurer3], [Insurer4], 
[Isurer5])
)     AS pvt

I expect the output to be a table with a single row for each GUID with the remaining columns populated where appropriate based on the input.

Answer 1

This is a shot in the dark. Not sure what columns in your query refer to the shorthand you mention. But using conditional aggregation you could do something like this.

select Id
    , Insurer1 = max(case when PolCount = 1 then BinderInsurerName end)
    , Perc1 = max(case when PolCount = 1 then BinderInsurerPerc end)
    , Flag1 = max(case when PolCount = 1 then LeadInsurer end)
    , Insurer2 = max(case when PolCount = 2 then BinderInsurerName end)
    , Perc2 = max(case when PolCount = 2 then BinderInsurerPerc end)
    , Flag2 = max(case when PolCount = 2 then LeadInsurer end)
    , Insurer3 = max(case when PolCount = 3 then BinderInsurerName end)
    , Perc3 = max(case when PolCount = 3 then BinderInsurerPerc end)
    , Flag3 = max(case when PolCount = 4 then LeadInsurer end)
    , Insurer4 = max(case when PolCount = 4 then BinderInsurerName end)
    , Perc4 = max(case when PolCount = 4 then BinderInsurerPerc end)
    , Flag4 = max(case when PolCount = 4 then LeadInsurer end)    
    , Insurer5 = max(case when PolCount = 5 then BinderInsurerName end)
    , Perc5 = max(case when PolCount = 5 then BinderInsurerPerc end)
    , Flag5 = max(case when PolCount = 5 then LeadInsurer end)
from
(       
    SELECT Policy.Id,
        BinderInsurer.[Percentage] As BinderInsurerPerc,
        BinderInsurer.LeadInsurer,
        Account.RegisteredCompanyName As BinderInsurerName,
        ROW_NUMBER() OVER (PARTITION BY Policy.Id ORDER BY BinderInsurer.[Percentage] DESC) AS PolCount
    FROM [Policy]
    Left Join Scheme On Scheme.Id = [Policy].SchemeId
    Left Join BinderAgreement On BinderAgreement.Id = Scheme.BinderAgreementId
    Left Join BinderSection On BinderSection.Id = Scheme.BinderSectionId
    Left Join BinderPeriod On BinderPeriod.Id = Scheme.BinderPeriodId
    Left Join BinderInsurer On BinderAgreement.Id = BinderInsurer.BinderAgreementId
    Left Join Account On Account.Id = BinderInsurer.AccountId
) x
group by x.Id