MySQL Dynamic SELECT COLUMN

Question

I'm looking for query that selects different column name, dynamically, by variable.

Suppose I have that contains my "dynamic columns" I'd like to select , something like this:

SELECT `ObjectID`,`ObjectLabel` FROM `modules_forms_objects`

+----------+---------------+
| ObjectID | ObjectLabel   |
+----------+---------------+
|       71 | Join Date     |
|       72 | Active        |
+----------+---------------+

Now, I've got one more table, which it's columns called by the "ObjectID" as above. Here:

SELECT `data_id`,`71`,`72` FROM `7`

+---------+---------------------+------+
| data_id | 71                  | 72   |
+---------+---------------------+------+
|       1 | 0000-00-00 00:00:00 | NULL |
+---------+---------------------+------+

I want to join a "Value" column to the first table, and this'll contain the Value from the match Column in the second table. (for example: for ObjectID #72 the value will be NULL).

Ultimately, I want my result to be like this:

+----------+---------------+--------------------+
| ObjectID | ObjectLabel   |       Value    |
+----------+---------------+--------------------+
|       71 | Join Date     |0000-00-00 00:00:00 |
|       72 | Active        |        NULL    |
+----------+---------------+--------------------+

When I used traditional "JOIN" I got the all columns for each row, and it seems kind of heavy.

Any ideas? Thanks a lot!

you are having lots and lots of coulmns, don't you?

itachi
– itachi

2013-08-25 18:50:21 +00:00
Commented Aug 25, 2013 at 18:50 — itachi
– itachi, Commented Aug 25, 2013 at 18:50

peterm · Accepted Answer · 2013-08-25 19:42:37Z

1

The base static query might look like this

SELECT m.ObjectID, m.ObjectLabel, q.Value
  FROM modules_forms_objects m LEFT JOIN
(
  SELECT objectid,
         CASE objectid 
            WHEN 71 THEN `71`
            WHEN 72 THEN `72`
         END value
    FROM `7` t CROSS JOIN
  (
    SELECT 71 objectid UNION ALL
    SELECT 72 
  ) c
) q 
    ON m.objectid = q.objectid

The idea is to first unpivot your 7 table and then outer join it with modules_forms_objects

Output:

| OBJECTID | OBJECTLABEL |               VALUE |
-----------|-------------|---------------------|
|       71 |   Join Date | 0000-00-00 00:00:00 |
|       72 |      Active |              (null) |

Here is SQLFiddle demo

If you don't have dozens and dozens of columns in 7 table I'd recommend to stick with the static query.

Now same thing with dynamic SQL

SET @sql = NULL, @sql1 = NULL, @sql2 = NULL;

SELECT GROUP_CONCAT(
           CONCAT('WHEN ''',  column_name, ''' THEN `', column_name, '`')
       SEPARATOR ' ')
  INTO @sql1
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_schema = SCHEMA()
   AND table_name = '7'
   AND column_name LIKE '7%'
 GROUP BY table_name;

SELECT GROUP_CONCAT(
           CONCAT('SELECT ''',  column_name, ''' objectid' )
       SEPARATOR ' UNION ALL ')
  INTO @sql2
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_schema = SCHEMA()
   AND table_name = '7'
   AND column_name LIKE '7%'
 GROUP BY table_name;

SET @sql = CONCAT(
'SELECT m.ObjectID, m.ObjectLabel, q.Value
  FROM modules_forms_objects m LEFT JOIN
(
  SELECT objectid,
         CASE objectid ', @sql1, ' END value 
    FROM `7` t CROSS JOIN
  (', @sql2, '
  ) c
) q
    ON m.objectid = q.objectid');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here is SQLFiddle demo

edited Aug 25, 2013 at 19:42

answered Aug 25, 2013 at 19:11

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Over a year ago

Thanks. Do you know if there is any way to this dynamic? if I'll do so, i'll have to use php to generate the CASE syntax, and i'm trying to avoid this.

peterm Over a year ago

@Dan You're welcome. See updated answer. I just showed how to do that using dynamic SQL. You can wrap it a stored procedure to simplify things on client (php) side.

peterm Over a year ago

@Dan If you feel like it was what you were looking for please, consider to accept the answer.

Arun Kumar M · Accepted Answer · 2013-08-25 18:57:05Z

1

USE SHOW COLUMNS FROM tab_name and use FIELD property to get the corresponding column.

answered Aug 25, 2013 at 18:57

Arun Kumar M

8489 silver badges14 bronze badges

Comments

skv · Accepted Answer · 2013-08-25 19:09:21Z

0

This would give all column names in a particular table

SELECT 
        GROUP_CONCAT(COLUMN_NAME)
FROM 
        INFORMATION_SCHEMA.COLUMNS
WHERE 
        table_name = 'mytable'

answered Aug 25, 2013 at 19:09

skv

1,8033 gold badges20 silver badges27 bronze badges

1 Comment

CBEK Over a year ago

Small improvement: SELECT GROUP_CONCAT(DISTINCT COLUMN_NAME ORDER BY COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'myTable'

Collectives™ on Stack Overflow

MySQL Dynamic SELECT COLUMN

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related