MySQL concat() to create column names to be used in a query?

by

I previously said that this couldn’t be done, but I was wrong. I ended up needing something like this myself so I looked around, and discovered that server-side prepared statements let you build and execute arbitrary SQL statements from strings.

Here is an example I just did to prove the concept:

set @query := (
  select concat(
    "select",
      group_concat(concat("\n  1 as ", column_name) separator ','),
    "\nfrom dual")
  from information_schema.columns
  where table_name="columns")
;
prepare s1 from @query
;
execute s1
;
deallocate prepare s1
;

More Related Contents:

  1. Can I concatenate multiple MySQL rows into one field?
  2. How to use GROUP_CONCAT in a CONCAT in MySQL
  3. String concatenation in MySQL
  4. MySQL select with CONCAT condition
  5. MySQL CONCAT returns NULL if any field contain NULL
  6. How to use IN & NOT IN same query [closed]
  7. SQL Print the name of all Employees together with the name of their supervisor
  8. MySQL: Large VARCHAR vs. TEXT?
  9. Check overlap of date ranges in MySQL
  10. Multiple-column foreign key in MySQL?
  11. What is the question mark’s significance in MySQL at “WHERE column = ?”?
  12. mysql – making a mechanism similar to Oracle’s seqences
  13. Get the latest date from grouped MySQL data
  14. UUID performance in MySQL?
  15. MySQL: NULL vs “”
  16. MySQL update a joined table
  17. Optimal MySQL settings for queries that deliver large amounts of data?
  18. mysql configuration stops at “starting server”
  19. MySQL Error 1264: out of range value for column
  20. Like Case Sensitive in MySQL
  21. What is the best way to delete old rows from MySQL on a rolling basis?
  22. What are the known ways to store a tree structure in a relational DB? [closed]
  23. Illegal mix of collations error in MySql
  24. No Entity Framework provider found for ‘MySql.Data.MySqlClient’ ADO.NET provider
  25. mysql equivalent data types
  26. Get difference in years between two dates in MySQL as an integer
  27. use a variable for table name in mysql sproc
  28. How to initialize mysql container when created on Kubernetes?
  29. Find duplicates in the same table in MySQL
  30. Auto-increment is not resetting in MySQL

Leave a Comment