MySQL concat() to create column names to be used in a query?
Answers
To create column names to be used in a query, you need to use a user-defined variable with the set command. The syntax is as follows −
SET @anyVariableName :=
(
SELECT CONCAT
(
"SELECT",
GROUP_CONCAT(CONCAT("\n 1 as ", COLUMN_NAME) SEPARATOR ','), "\n FROM DUAL")
FROM INFORMATION_SCHEMA_COLUMNS
WHERE TABLE_NAME= ‘yourTableName’
);
Now prepare the statement using the PREPARE command. The syntax is as follows −
PREPARE anyVariableName from @anyVariableName;
Execute statement using EXECUTE command. The syntax is as follows −
EXECUTE anyVariableName;
Deallocate the prepared statement using DEALLOCATE command. The syntax is as follows −
DEALLOCATE PREPARE anyVariableName;
We will now create a table with two columns and column will be used in the concat query. The query to create a table is as follows −
mysql> create table ConcatenationWithUserDefinedVariable
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> User_Id int,
-> PRIMARY KEY(Id)
-> );
Query OK, 0 rows affected (1.14 sec)
Use the above syntax for column names to be used in the query. The query is as follows −
mysql> set @q := (
-> select concat(
-> "select",
-> group_concat(concat("\n 1 as ", column_name) separator ','),
-> "\nfrom dual")
-> from information_schema.columns
-> where table_name = 'ConcatenationWithUserDefinedVariable');
Query OK, 0 rows affected (0.01 sec)
The query to prepare the above user-defined variable is as follows −
mysql> prepare stmt from @q;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
Now execute the above-prepared statement. The query is as follows −
mysql> execute stmt;
The following is the output −
+----+---------+
| Id | User_Id |
+----+---------+
| 1 | 1 |
+----+---------+
1 row in set (0.00 sec)
In the end, you need to deallocate the prepared statement. The query is as follows −
mysql> deallocate prepare stmt;
Query OK, 0 rows affected (0.00 sec)