Using custom fields - niffty SQL PIVOT

[neil.interactit]

Rather than always be asking questions, I thought it time to share something back. An obscure bit of SQL that makes using custom fields easy!

I have added the following custom fields in a plugin ...

customfields.png (14.15 KiB) Viewed 2611 times

As debtor records are saved, each custom value is saved to a separate record, making it more complex to work with all the values for a debtor as a single set.

The following pivot query makes all this easy ...

Code: Select all

SELECT *
FROM
(
   SELECT p.DebtorID, p.Contents, pd.SettingName
   FROM DB_CustomSettingValues p
   LEFT OUTER JOIN DB_CustomSetting pd
   ON pd.SettingID = p.SettingID
) tbl
PIVOT ( MIN(tbl.Contents) FOR tbl.SettingName in ([ppOnPaymentPlan],[ppRepeatEvery],[ppPeriod],[ppNextActivated],[ppHasEndDate],[ppEndDate],[ppAmount],[ppAllRemaining]) ) piv
WHERE [ppOnPaymentPlan] = 'True'


Producing a nice result set like:

Code: Select all

    'DebtorID               ppOnPaymentPlan   ppRepeatEvery   ppPeriod   ppNextActivated   ppHasEndDate   ppEndDate   ppAmount   ppAllRemaining
    'A7B1A08F9B214B84AB67   True           21               Days       04/09/2015       True           05/10/2015   10           NULL
    'd22adfe8cd6948238ad7   True           2               Days       06/10/2015       True           31/10/2015   34           True
    'ED29A82C136345C6BEBC   True           1               Months       05/10/2015       NULL           NULL       NULL       True
    'F8274E835D92420691A7   True           14               Days       07/10/2015       NULL           NULL       23           NULL


[Scott.Pearce]

Nice!