How to use query results as column names in a SELECT statement

Question

My ultimate goal is to create a pivot table view in MySQL with dynamic columns based on the contents of another table. At the moment, I am trying to continue when artfulsoftware is not working; right now I can query for the results that give me the desired column names. Unfortunately, I lost information on how to use the results as column names in a SELECT statement. I suspect MySQL variables will be useful, but I can't figure it out.

To clarify the problem, let's say I have a table like:

+---------------------------------------------------+ | countpivotarg | +---------------------------------------------------+ | ,SUM(IF(domain = "test.com",1,0)) AS `test.com` | | ,SUM(IF(domain = "test2.com",1,0)) AS `test2.com` | +---------------------------------------------------+ 

I want to create a select statement that looks like this:

 SELECT id, meta_id, SUM(IF(domain = "test.com",1,0)) AS `test.com`, SUM(IF(domain = "test2.com",1,0)) AS `test2.com` FROM myTable; 

How can I do it?

Answer 1

You can use MySQL Server prepared statements to create dynamic queries from string variables.

Example:

 SELECT domain INTO @colname FROM myTable LIMIT 1; SET @s = CONCAT('SELECT `',@colname,'` FROM myTable'); PREPARE stmt FROM @s; EXECUTE stmt; 

Note: backticks are required if column names contain spaces.

Answer 2

In SQL, column names must be corrected at the time the query is prepared — no exceptions. When developing a dynamic summary query, when you don’t know the columns, it is required that you write the application code anyway. You have a choice between using the preprocessing or postprocessing method:

• Preprocessing: Write a query to get a list of different values. Then use them as column names and create a new dynamic SQL query.

• Postprocessing: Write a query to retrieve the data as a result without rotation, and then extract all the data and rotate to a different format.