March 5, 2015 at 10:36 am
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)
March 5, 2015 at 1:00 pm
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