MySQL: How do you concat a column name with AS?-CodePudding

I am trying to get it so that I can use a variable in the column name but it doesn't seem to be working.

select 
    v.name as concat(@person,'\'s Vendor')
    ,sum(t.amount) as 'Total' 
from 
    total t 
inner join vendor v 
    on v.d = t.d 
where 
    t.who = @person 
    and v.name is not null 
    and t.sub_cat not like 'Payment' 
    and year(date) = (year(sysdate())-1) 
    and amount > 0 
group by 
    v.name 
order by 
    sum(amount) 
desc limit 20;

The above throws an error but below works.

select 
    v.name as 'John\'s Vendor'
    ,sum(t.amount) as 'Total' 
from 
    total t 
inner join vendor v 
    on v.d = t.d 
where 
    t.who = @person 
    and v.name is not null 
    and t.sub_cat not like 'Payment' 
    and year(date) = (year(sysdate())-1) 
    and amount > 0 
group by 
    v.name 
order by 
    sum(amount) 
desc limit 20;

Basically, I want to be able to add the person variable and some more text as the column name to better identify the purpose of the data, in this case, who the data is about. Also, I would like to use this as a stored procedure:

create procedure GetTotals(IN person varchar(10));
delimiter //
begin
    above working code
end //
delimiter ;

CodePudding user response：

Why not just add the parameter as its own column with a fixed column name. Your return process will be responsible for presenting and can just use that extra column as the "who's" it for purposes. Ex:

select 
      v.name,
      sum(t.amount) as 'Total',
      concat(@person,'\'s Vendor') as ForWho
   from

Yes, every row will return a result of ex: "John's Vendor" in the column ForWho, but at least you wont have to format that in the return set, you have the column.

CodePudding user response：

Identifiers, including aliases, must be fixed at the time the query is parsed. You can't make an alias that depends on an expression.

You could make a dynamic SQL query by formatting an SQL statement using your @person variable:

SET @sql = CONCAT(
 'select v.name as `', concat(@person,'\'s Vendor'), '`,',
 ...
 ' where t.who = ? ',
 ...
);
PREPARE stmt FROM @sql;
EXECUTE stmt USING @person;
DEALLOCATE PREPARE stmt;

The value of @person can be set as the parameter where the ? placeholder appears in the prepared query syntax. But the usage in the column alias cannot be parameterized. So you just have to be careful that the value of @person is safe to use in that position.

You should delimit the alias with back-ticks like any other identifier, to protect from syntax errors if @person contains whitespace or punctuation characters.

CodePudding user response：

You can use dynamic sql for such queires

SET @sql = CONCAT("select 
    v.name as '",@person,
    "\\'s Vendor'
    ,sum(t.amount) as 'Total' 
from 
    total t 
inner join vendor v 
    on v.d = t.d 
where 
    t.who = @person 
    and v.name is not null 
    and t.sub_cat not like 'Payment' 
    and year(date) = (year(sysdate())-1) 
    and amount > 0 
group by 
    v.name 
order by 
    sum(amount) 
desc limit 20;");
PREPARE stmt FROM @sql;
EXECUTE stmt;    
DEALLOCATE PREPARE stmt;

Keep in mind thsi can be used for sql injection, so a white list for the name is in order