Pivot queries are very useful, but it's really common for the pivot to produce a ridiculously large list of columns. Take this example: you have a table listing lab test results. There are a lot of potential values for the type of test. Any given patient only has a finite number of test records. if you write the standard PIVOT, the list of columns will be any available test, and does not by default do anything smart to limit to only those columns with data in the selected rows. I was hoping parameterized queries would give me an out - no luck.

Below is an example of SQL I'm trying to use. Is there a way to modify this such that the list of columns will be anything less than every possible value in the whole table? Thanks in advance:

PARAMETERS(SPECIES CHAR default NULL, LOCUS CHAR default NULL, CATEGORY CHAR default NULL)

SELECT
  a.analysis_id,
  a.lineages,
  sum(a.percent) as percent

FROM sequenceanalysis.alignment_summary_by_lineage a

GROUP BY a.analysis_id, a.lineages
PIVOT percent BY lineages in (
  SELECT DISTINCT r.lineage FROM sequenceanalysis.ref_nt_sequences r
  WHERE r.lineage is not null

--this is my attempt to filter the list of allowable columns based on the query params. does not appear to work.
    AND (r.species = SPECIES OR SPECIES is null)
    AND (r.locus = LOCUS OR LOCUS is null)
    AND (r.category = CATEGORY OR CATEGORY is null)
  ORDER BY r.lineage
)