CONCATS for column names, column aliases

Question

I'm trying to run a query for each day within a month. The portion of the query for the first of the month looks like this, and it works:

CASE 
WHEN CONCAT(
    Shifts.d1a,
    Shifts.d1b, 
    Shifts.d1c, 
    Shifts.d1d, 
    Shifts.d1e, 
    Shifts.d1f) = "" THEN "-"
ELSE
  CONCAT (
    IF (Shifts.d1a='Yes','A',''),
    IF (Shifts.d1b='Yes','B',''),
    IF (Shifts.d1c='Yes','C',''),
    IF (Shifts.d1d='Yes','D',''),
    IF (Shifts.d1e='Yes','E',''),
    IF (Shifts.d1f='Yes','F','')
    ) END AS d1,

The second day of the month would look the same, except that all the "d1" parts of the table references would be "d2," and the "END AS d1" would be "END AS d2."

I inherited the table structure; each d[n][x] gets a value of "Yes" or "". There are no nulls involved. The goal is to take separately fielded items within a day - i.e. d[n]a, d[n]b, d[n]c, etc, and concatenate them into (e.g.) "ABF" for each field that has a yes value, and if all fields are empty generate a hyphen, in column d[n].

Like I said, this works if I run 31 executions of nearly identical code. That just strikes me as ugly and unnecessary. But I've run up against the apparent wall of using concatenated strings as column names within SELECTs, as well as using variables as part of a column alias in the output. So this doesn't work:

BEGIN
  DECLARE vcounter INT DEFAULT 0;
  DECLARE vcolumn VARCHAR (10);
  DECLARE vcolumna VARCHAR (64);
  DECLARE vcolumnb VARCHAR (64);
  DECLARE vcolumnc VARCHAR (64);
  DECLARE vcolumnd VARCHAR (64);
  DECLARE vcolumne VARCHAR (64);
  DECLARE vcolumnf VARCHAR (64);
shift_loop: REPEAT
SET vcounter = vcounter + 1;
SET vcolumn = CONCAT ('d',vcounter);
SET vcolumna = CONCAT ('Shift.',vcolumn,'a');
SET vcolumnb = CONCAT ('Shift.',vcolumn,'b');
SET vcolumnc = CONCAT ('Shift.',vcolumn,'c');
SET vcolumnd = CONCAT ('Shift.',vcolumn,'d');
SET vcolumne = CONCAT ('Shift.',vcolumn,'e');
SET vcolumnf = CONCAT ('Shift.',vcolumn,'f');
SELECT 
  CASE 
   WHEN CONCAT(
        vcolumna,
        vcolumnb,
        vcolumnc,
        vcolumnd,
        vcolumne,
        vcolumnf) = '' THEN '-'
    ELSE 
        CONCAT(
        vcolumna,
        vcolumnb,
        vcolumnc,
        vcolumnd,
        vcolumne,
        vcolumnf
        ) 
        END
          AS vcolumn;
UNTIL vcounter = 1
END REPEAT;
END

Yes, I know that the CASE statement in the procedure is unnecessary -- I could just assign the results of the final CONCAT to a variable and test it against an empty value. I just left this as-is to give a better idea of what I want to do.

The result is that for each row I get a single column called "vcounter," with its contents being "Shift.d1aShift.d1bShift.d1c" etc. Does anyone have any suggestions?

EDITED TO ADD

OK, thanks, I checked out the suggested answer, but I can't figure out how to make it work with the WHILE statement I need. Here's what I changed my code to:

 BEGIN  
 SET @vcounter = 0;  
 SET @vcounter = @vcounter + 1;  
 SET @vtable = 'Table_X';  
 SET @vcolumn = CONCAT ('d',@vcounter);  
 SET @vcolumna = CONCAT (@vtable,'.',@vcolumn,'a');  
 SET @vcolumnb = CONCAT (@vtable,'.',@vcolumn,'b');  
 SET @vcolumnc = CONCAT (@vtable,'.',@vcolumn,'c');  
 SET @vcolumnd = CONCAT (@vtable,'.',@vcolumn,'d');  
 SET @vcolumne = CONCAT (@vtable,'.',@vcolumn,'e');   
 SET @vcolumnf = CONCAT (@vtable,'.',@vcolumn,'f');  
 SET @vshifts = CONCAT  
 (@vcolumna,',',@vcolumnb,',',@vcolumnc,',',@vcolumnd,',',@vcolumne,',',@vcolumnf);  
 SET @vquery = CONCAT ('SELECT CASE WHEN CONCAT (',  
                  @vshifts,  
                  ') ="" THEN "-" ELSE   
                  CONCAT (IF(',@vcolumna,' = "Yes","A",""),  
                          IF(',@vcolumnb,' = "Yes","B",""),  
                          IF(',@vcolumnc,' = "Yes","C",""),  
                          IF(',@vcolumnd,' = "Yes","D",""),  
                          IF(',@vcolumne,' = "Yes","E",""),  
                          IF(',@vcolumnf,' = "Yes","F","")  
                  )END AS ',@vcolumn,' FROM ',@vtable);  
 PREPARE stmt FROM @vquery;  
 EXECUTE stmt;  
 END

This works nicely for a single day. Problem is, anywhere I insert a WHILE or REPEAT statement, I get bad results. The closest I've found ends up producing results that look like

 d1  
 D  
 F  
 A  
 B  

 d2  
 A  
 AB  
 C  
 -

etc. What I want instead is

 d1    d2  
 D     A
 F     AB
 A     C
 B     -

I obviously don't know much about what I'm doing here, so I really appreciate any help anyone can provide. Thanks.

While I do not see your real table structure, it looks like you have a serious table design problem. Can you change the tables? — juergen d, Jan 18 '17 at 09:53
use dynamic sql via prepared statements and also consider @juergend 's comment. — Shadow, Jan 18 '17 at 09:56
Possible duplicate of [mysql field name from variable](http://stackoverflow.com/questions/4428761/mysql-field-name-from-variable) — Shadow, Jan 18 '17 at 09:58
Sorry, I inherited this structure and I have to leave it in place for backward compatibility. — Fred760, Jan 21 '17 at 04:58
Sometimes I imagine there's one individual developer in the world who implements bad database designs that break 1NF, and then he quickly leaves to go work at another company to do the same thing. — Bill Karwin, Jan 24 '17 at 22:32

score 0 · Answer 1 · answered Jan 24 '17 at 22:26

OK, solved it myself. Here's what works:

 BEGIN
 SET @vcounter = 0;
 SET @vtable = 'tablename';
 SET @vquery = 'SELECT
  last_name As Last_Name,
  first_name As First_Name, ';
 shift_loop: REPEAT
 SET @vcounter = @vcounter + 1;
 SET @vcolumn = CONCAT ('d',@vcounter);
 SET @vcolumna = CONCAT (@vcolumn,'a');
 SET @vcolumnd = CONCAT (@vcolumn,'b');
 SET @vcolumnp = CONCAT (@vcolumn,'c');
 SET @vcolumne = CONCAT (@vcolumn,'d');
 SET @vcolumnt = CONCAT (@vcolumn,'e');
 SET @vcolumns = CONCAT (@vcolumn,'f');
 SET @vshifts = CONCAT (@vcolumna,',',@vcolumnb,',',@vcolumnc,',',@vcolumnd,',',@vcolumne,',',@vcolumnf);
 SET @vquery = CONCAT (@vquery,' CASE WHEN CONCAT (',
                       @vshifts,
                       ') ="" THEN "-" ELSE 
                       CONCAT (IF(',@vcolumna,' = "Yes","A",""),
                              IF(',@vcolumnb,' = "Yes","B",""),
                              IF(',@vcolumnc,' = "Yes","C",""),
                              IF(',@vcolumnd,' = "Yes","D",""),
                              IF(',@vcolumne,' = "Yes","E",""),
                                  IF(',@vcolumnf,' = "Yes","F","")
                       ) END AS ',@vcolumn,',');
 Until @vcounter = 31
 END REPEAT;
 SET @vquery = CONCAT (
     @vquery,'
     comment AS Comment,
     _submitted_ AS Submitted
     FROM ',@vtable);
 PREPARE stmt FROM @vquery;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 END

CONCATS for column names, column aliases

1 Answers1