SQL join row contents to another table's column names

Question

Assuming I have the following two tables:

       **Table A**       

ID   Day   Month   Year           
------------------------       
1    1      1       1900
3    13     3       2009
49   28     2       1984


                      **Table B**     

ID   ABC_1_1_1900   ABC_2_1_1900 ...  ABC_31_12_2100           
-------------------------------- ... ---------------       
1        431            15449             98565
2                
3                       ....
.
.
n                                         ....

and would like to get the following table:

      **Table C**     

ID        ABC           
------------------------       
1         431
3         (value B.ABC_13_3_2009 for ID=3)
49        (value B.ABC_28_2_1984 for ID=49)

What essentially I'm trying to achieve is, get a subset of Table B by matching the row contents of A to the column names of B and inner joining on the IDs.

The equivalent would be

SELECT A.ID, B.CONCAT('ABC_', A.Day, '_', A.Month, '_', A.Year) AS ABC
FROM A
INNER JOIN B ON A.ID=B.ID

which unfortunately doesn't work. Any ideas greatly appreciated!

If your `ID` is `UNIQUE` then your `INNER JOIN` would bring back the value in `Table B`. Why are you trying to `CONCAT()` if you only want the value? — S3S, Jun 02 '16 at 21:17
Because I want only one value out of all the Table B columns (let's say Table B has thousands of columns), which corresponds to the contents in Table A. — spater, Jun 02 '16 at 21:33
See normalization. A database table is NOT a spreadsheet. And data types were invented for good reason. — Strawberry, Jun 02 '16 at 22:36

score 0 · Answer 1 · edited May 23 '17 at 11:52

What you are trying to do is to create an SQL statement from a variable. How this is done depends on the SQL technology you are using. From the tag, I assume that you use MySQL. With MySQL you need to make a so called Prepared Statement (Attention: They say it's quite hacky, see: mysql field name from variable). With the prepared statement, you will be able to pass the column contents to your SQL statement. All this would look like the following:

Create SQL String:

SET @s = CONCAT('SELECT A.ID, B.ABC_', A.Day, '_', A.Month, '_', A.Year, ' AS ABC FROM A INNER JOIN B ON A.ID=B.ID');

Prepare Statement and execute:

PREPARE stmt FROM @s; EXECUTE stmt;

Note: Be aware that this can be prone to errors, since the number in the column (for example day) must always be formatted the same as the number in the column name (ABC_X_Y_Z).

SQL join row contents to another table's column names

1 Answers1