Pivoting coursecustomfields from Long to Wide

Problem

The coursecustomfields table is stored in **long (entity-attribute-value) format** — one row per (courseid, label) pair, with the actual value held in the value column. This shape is flexible (any number of custom fields, no schema change required to add a new one) but it is awkward to query:

• Filtering on "courses where Department = Sales AND Difficulty = Advanced" requires multiple self-joins or subqueries.
• BI tools generally expect one row per course with custom fields as columns.

Solution

-- Pivot coursecustomfields: one row per course, one column per custom field
SELECT
  ccf.courseid,
  MAX(CASE WHEN ccf.label = 'Department'     THEN ccf.value END) AS department,
  MAX(CASE WHEN ccf.label = 'Difficulty'     THEN ccf.value END) AS difficulty,
  MAX(CASE WHEN ccf.label = 'Duration (hrs)' THEN ccf.value END) AS duration_hours,
  MAX(CASE WHEN ccf.label = 'Product Line'   THEN ccf.value END) AS product_line
FROM coursecustomfields ccf
WHERE ccf.statusgroup = 'a'
GROUP BY ccf.courseid;

-- Enrich the courses table with pivoted custom fields
WITH ccf_pivot AS (
  SELECT
    courseid,
    MAX(CASE WHEN label = 'Department' THEN value END) AS department,
    MAX(CASE WHEN label = 'Difficulty' THEN value END) AS difficulty
  FROM coursecustomfields
  WHERE statusgroup = 'a'
  GROUP BY courseid
)
SELECT
  c.id          AS course_id,
  c.title       AS course_title,
  ccf.department,
  ccf.difficulty
FROM courses c
LEFT JOIN ccf_pivot ccf ON ccf.courseid = c.id
WHERE c.statusgroup = 'a';

How it works

• MAX(CASE WHEN label = '<X>' THEN value END) collapses the per-label rows into one column per label. MAX is used because aggregation is required after GROUP BY — MIN/ANY_VALUE work equally well since there's only one value per (courseid, label).
• GROUP BY courseid produces the one-row-per-course shape.
• statusgroup = 'a' on both the pivot and the outer courses filter ensures we ignore archived/deleted custom field rows and archived courses.

Variations

• **Numeric custom fields:** wrap with CAST(... AS DECIMAL) if you need to do math on a custom field stored as a string (value is varchar).
• **Dynamic column set:** the MAX(CASE WHEN ...) pattern requires you to enumerate labels statically. For a truly dynamic column set, generate the pivot SQL in your BI tool or use a long-format export.
• **Per-instance labels:** custom field labels vary per tenant. Run SELECT DISTINCT label FROM coursecustomfields WHERE companyid = '<tenant>' to discover them before writing the pivot.