Dynamic Column name in MYSQL

Question

Yesterday I screwed up by asking question which had dupe column name by accident. This caused interesting solutions. So I will ask same question here again, but this time I will directly mysql dump, so there are no confusion.

CREATE TABLE `tbl1` (
  `UserID` char(15) DEFAULT NULL,
  `col1` char(15) DEFAULT NULL,
  `col2` char(15) DEFAULT NULL,
  `col3` char(15) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tbl2` (
  `UserID` char(15) DEFAULT NULL,
  `col4` char(15) DEFAULT NULL,
  `col5` char(15) DEFAULT NULL,
  `col6` char(15) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO tbl1
  (`UserID`, `col1`, `col2`, `col3`)
VALUES
  ('user1', 'one', 'two', 'three');

INSERT INTO tbl1
  (`UserID`, `col1`, `col2`, `col3`)
VALUES
  ('user1', 'oneone', 'twotwo', 'threethree');

INSERT INTO tbl2
  (`UserID`, `col4`, `col5`, `col6`)
VALUES
  ('user1', 'col4name', 'col5name', 'col6name');

End result what I am looking for would look like this, after mysql query.

CREATE TABLE `endresult` (
  `UserID` char(15) DEFAULT NULL,
  `col4name` char(15) DEFAULT NULL,
  `col5name` char(15) DEFAULT NULL,
  `col6name` char(15) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

You will see that user1 has on tbl2 under col4 record "col4name" which was added by user1 for his/hers custom name for that particular column. Now I would like to see these custom column names displayed like they are displayed on "endresult" table.

I know this would be preferred to do at the end using php to display, but I really need to do this in mysql side. Thanks again

Answer 1

I think executing the SQL statement as a string (dynamically) may be your best bet. Please be careful with respect to SQL injection and the data to expect to use within this SQL statement.

Something like:

SET @qry = (
            SELECT CONCAT('SELECT t1.userid, 
                           t1.col1 AS "', t2.col4, '", 
                           t1.col2 AS "', t2.col5, '",
                           t1.col3 AS "', t2.col6, '"
                    FROM tbl1 t1') AS QUERY_TEXT                      
            FROM tbl2 t2 
            WHERE t2.userid = @userId
           );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This query will return data from tbl1, but col1, col2, and col3 are aliased by the values within the matching tbl2 record given a specific userid @userId.

Therein lies the issue, I can't seem to figure out what to expand on this issue to make it apply to multiple users in a single SELECT statement. This makes sense, because it's not possible to line up the columns correctly when they're named different (plus, I believe that this is impossible to do in SQL).

Answer 2

I think that you would like the column name to be dynamically defined based on the contents of tbl1 and tbl2.

This is not possible, the closest you can get to this, is to dynamically craft a SQL statement with distinct alliases for the columns (based on a previous SQL query to find the proper values). This of course would be limited to the names for ONE given user since, several users may have conflicting names, but the query will take only one allias per column... (That is BTW one, though not the main reason, why this can't be done directly in SQL).

EDIT NOTE: David Andres' reply shows one way of doing such a two step query.

Answer 3

This is from other board.... Not sure how to apply for my situation ?

Maybe something like this (done for mysql database, you should modify this for your joins):

mysql> select user, CASE WHEN user='root' THEN host WHEN user='drupal' THEN password END FROM user WHERE user<>''; +------------------+----------------------------------------------------------------------+ | user | CASE WHEN user='root' THEN host WHEN user='drupal' THEN password END | +------------------+----------------------------------------------------------------------+ | root | localhost | | root | laptop | | root | 127.0.0.1 | | debian-sys-maint | NULL | | drupal | *7AFEAE5774E672996251E09B946CB3953FC67656 | | drupal | | +------------------+----------------------------------------------------------------------+

You should make something like:

SELECT .... ON tbl1 JOIN tbl2 ON tbl1.col1=tbl2.col2 ...