Using &lexical Anchors for Dynamic PIVOT Tables

You should use the &lexical parameter reference for dynamic pivot tables. The &lexical anchor is highly versatile and is used to insert variable SQL snippets for elements like dynamic columns, ORDER BY, GROUP BY, or, in your case, the dynamic list of periods columns required by the PIVOT clause.

Yes, that is correct. The &gl_period_pivot reference inside the pivot clause will be replaced at execution time by the parameter SQL text, which generates the comma-separated list of periods (e.g., ‘Jan-20’ jan_2020, ‘Feb-20’ feb_2020, …).

If the parameter value is left blank, the corresponding &lexical reference is completely removed before SQL execution, although for a PIVOT clause, leaving the column list blank might cause a syntax error depending on your overall SQL structure.