How to Dynamically Format Column Headers with Substitution Variables

Question

I have a table that has products defined by an identifying code that begins with a number and ends with a letter such as 1A, 3B, 7C, etc. The query assembles the data I need about these with a PIVOT statement. Unfortunately, in Oracle we are not allowed to begin aliases with a number. So far, I have successfully made the query work around that by outputting data that is essentially the following:

SELECT * FROM MyTable
PIVOT ( COUNT(Product) FOR ProductName IN (&product_statement) )

where the substitution variable product_statement was dynamically generated with the assistance of the same process described here: How do I set LINESIZE and PAGESIZE with a substitution variable?. This is a representative result:

NAME     | A1 | B3 | C7
Product A| 1  | 2  | 1   
Product B| 3  | 1  | 2

Now I am trying to turn those product headings back around. Again with the substitution variable process, I created another variable named &column_statement that looks like this (each line is separated by CHR(10)):

COLUMN A1 TEMP FORMAT A3 HEADING ‘1A’
COLUMN B3 TEMP FORMAT A3 HEADING ‘3B’
COLUMN C7 TEMP FORMAT A3 HEADING ‘7C’

My thought process was that I could do the following in SQL*Plus:

&column_statement

SELECT * FROM MyTable
PIVOT ( COUNT(Product) FOR ProductName IN (&product_statement) )
/

but it gives me the following:

SP2-0734: unknown command beginning “&column_st…” – rest of line ignored.

NAME     | A1 | B3 | C7
Product A| 1  | 2  | 1   
Product B| 3  | 1  | 2

What do I have to do to dynamically set the column headers?

SQL\*Plus cannot recursively expand variables into SQL\*Plus commands and execute them. You would need to execute a valid command, even if it contained variables and you called it dynamically. — William Robertson, Oct 20 '18 at 11:04
You would have to `spool` the commands to a temporary script file and execute that, maybe using `set termout off feedback off heading off` etc to suppress unwanted output. It gets messy. — William Robertson, Oct 20 '18 at 11:18
That's a good idea, I'll try that when I am back in the office on Monday and let you know how it goes. Unfortunately, the whole thing is a bit messy, that's why I'm trying to make a script to handle this once instead of hard coding it over and over as things change. — SandPiper, Oct 20 '18 at 11:25
@WilliamRobertson, I took your advice and found a way to make it work. I detailed it in my answer below. Thanks for your help, I appreciate it! — SandPiper, Nov 21 '18 at 20:34

score 0 · Accepted Answer · answered Nov 21 '18 at 20:33

I figured it out based on William Robertson's suggestion. I spooled the data into an SQL file, then ran that file I just created within the script. It flows like this:

SET HEADING OFF
SET PAGESIZE 0
SPOOL /file_location/column_headers.sql

-- SELECT statement to dynamically make the column header string. Example output:
-- COLUMN A1 TEMP FORMAT A3 HEADING ‘1A’
-- COLUMN B3 TEMP FORMAT A3 HEADING ‘3B’
-- COLUMN C7 TEMP FORMAT A3 HEADING ‘7C’

SPOOL OFF

SET HEADING ON
SET PAGESIZE 100
SET TERMOUT ON

@/file_location/column_headers.sql

-- Now run the other query that uses these headers
SELECT …

I wouldn't call it the most elegant solution, but it definitely did the trick and has reduced a lot of hard coded queries.

How to Dynamically Format Column Headers with Substitution Variables

1 Answers1