Thanks for the reply Nevyn.
I think it might help if you took a step back and explained what you are trying to achieve overall.
Sounds like you like your query fine but want the same query with other tables. Why?
I had to write basically the same script 46 times and change the columns that are used because each specialty (like Allergy and Cardiology in the example) has its own set of measures that I need to unpivot using CROSS APPLY. Not only would that take a long time but it's error prone. The measures (column names) are often long, embedded with spaces and full of arcane text. I know there should be a better way to make this dynamic but I don't know how. And the table names could change next month but what's far more likely is that the measure names (columns) will change. An example of a measure name is 'Allergy IP ED Routine Care'.
Are you trying to avoid having to rewrite the query 30 times?
Yes and to reduce error from me miskeying or missing one.
Is the query for a procedure that takes table name as a parameter?
Not yet. Should it? And can I also have a way to read the names of the columns from the table?
Do all the tables need to be unpivoted in one query together? And if so, can the list of tables change dynamically?
The way I've done it is to have 46 separate queries that I can turn into stored procedures, each one of which will be the dataset for an SSRS report. It is the first time I have made separate reports for each but if I made one report with different specialties the columns would be different for each. Is there a better way. i feel like what I'm doing now with work but that I've written an inelegant kludge that will be a nightmare for me to manage and even worse if someone tries to use it in the future. As to whether the table names change : maybe not but possible. Will the column names change next month? Very likely. I import from Excel workbook that another company provides. I import each sheet to a separate table (all at once with SSIS import wizard in SSMS). Even if they put another space in a column name, it will be "different".
Thanks again for any help or advice you can provide!