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?

Are you trying to avoid having to rewrite the query 30 times?

Is the query for a procedure that takes table name as a parameter?

Do all the tables need to be unpivoted in one query together? And if so, can the list of tables change dynamically?