How to Join Pivot results with dynamic columns

  • I have a lookup table, as below. Each triggercode can have several service codes.

    TriggerCodeServiceCode

    BBRONZH BBRZFET

    BBRONZH RDYNIP1

    BBRONZP BBRZFET

    BCSTICP ULDBND2

    BCSTMCP RBNDLOC

    I then have a table of accounts, and each account can have one to many service codes. This table also has the rate for each code.

    AccountServiceCodeRate

    11518801DSRDISC -2

    11571901BBRZFET 5

    11571901RBNDLOC 0

    11571901CDHCTC 0

    17412902CDHCTC1 0

    14706401ULDBND2 2

    14706401RBNDLOC 3

    What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.

    I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.

    What I would like to get is just the service codes and the appropriate trigger code for each account.

    Hopefully I'm clear enough in how I have explained this.

    Any help is always greatly appreciated.

    SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode

    FROM TriggerTable

    FOR XML PATH(''), TYPE

    ).value('(./text())[1]', 'VARCHAR(MAX)')

    ,1,2,'')

    set @query = 'SELECT Account_Number, TriggerCode, ' + @cols + '

    FROM

    (

    SELECT Account_Number,

    TriggerCode,

    Service_Code,

    SERVICE_RATE

    FROM TriggerTable AS a INNER JOIN AcctDetails AS b

    ON a.ServiceCode = b.ServiceCode

    ) x

    PIVOT

    (

    SUM(Service_Rate) FOR Service_Code in (' + @cols + ')

    ) p '

    execute(@query)

  • So if your query worked, what would the output from your sample look like?

    It sounds like you want a query where each row has a different number of columns

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply