Until fairly recently, when I came across an interesting 'pivot' query methodology for SQL. It's been around for a while, but I only discovered it recently.
So I thought I'd share an example. This one is for custom task details in Service Manager, but the same method will work with any of them.
- Code: Select all
ALTER VIEW [dbo].[AERP_SM_TaskDetails] AS
SELECT TaskID
, MAX(CASE WHEN (SettingID = 'Setting-00') THEN Contents ELSE NULL END) AS Condition
, MAX(CASE WHEN (SettingID = 'Setting-01') THEN Contents ELSE NULL END) AS WarrantySeal
, MAX(CASE WHEN (SettingID = 'Setting-02') THEN Contents ELSE NULL END) AS ProofOfPurchase
, MAX(CASE WHEN (SettingID = 'Setting-03') THEN Contents ELSE NULL END) AS DateOfSale
, MAX(CASE WHEN (SettingID = 'Setting-04') THEN Contents ELSE NULL END) AS Notes
, MAX(CASE WHEN (SettingID = 'Setting-05') THEN Contents ELSE NULL END) AS Replaced
, MAX(CASE WHEN (SettingID = 'Setting-06') THEN Contents ELSE NULL END) AS TBR
, MAX(CASE WHEN (SettingID = 'Setting-07') THEN Contents ELSE NULL END) AS Abused
, MAX(CASE WHEN (SettingID = 'Setting-08') THEN Contents ELSE NULL END) AS BER
, MAX(CASE WHEN (SettingID = 'Setting-09') THEN Contents ELSE NULL END) AS NFF
FROM SM_TaskCustomSettingValues
GROUP BY TaskID
You need to replace the values 'Setting-00' to 'Setting-09' with the actual SetttingID value from your custom field definition table. Adding additional columns is really trivial, just copy & paste one of the lines and replace the relevant bits.
Technically, it should be possible to join on to the field definition table and use the names of the fields, but I figured for most people there'd be little benefit, and this way works faster too.
In practice, I've also added 'outer' tables and fields to the views to make them a bit more useful, but this logic remains the same.
You could also fairly easily add data type conversions into the view if necessary.
/Ryan