代码之家 › 专栏 › 技术社区 › hud

组合列名导致错误

sql-server-2005 sql-server sql

0

hud · 技术社区 · 9 年前

我有两列要合并并显示数据,我尝试如下

select 
    case 
       when status = 'R' 
          then 'Resign'
       when status = 'A' 
          then 'Active' 
    end as status1, 
    Program_name + ' ' + emp_card_no as program_details,
    * 
from 
    GetEmployeeDetails
where 
    emp_name = 'ABHAY ASHOK MANE'
    and STATUS = 'A' 
order by
    EMP_NAME

但我得到一个错误:

将数据类型varchar转换为数字时出错。

以下是可用的示例数据

5 回复 | 直到 9 年前

1

2

GuidoG 9 年前

试试这个:

select 
  case when status='R' then 'Resign'
       when status='A' then 'Active' 
  end as status1, 
  Program_name + ' (' + convert(varchar, emp_card_no) + ') ' as program_details,
* 
from GetEmployeeDetails
Where emp_name ='ABHAY ASHOK MANE'and STATUS= 'A' ORDER BY EMP_NAME

2

PaweÅ Dyl 9 年前

如果需要括号,请使用以下内容:

select 
  case when status='R' then 'Resign'
       when status='A' then 'Active' 
  end as status1, 
  Program_name + ' (' + convert(varchar(10), emp_card_no)+')' as program_details,
* 
from GetEmployeeDetails
Where emp_name ='ABHAY ASHOK MANE'and STATUS= 'A' ORDER BY EMP_NAME

3

2

Sandip - Frontend Developer 9 年前

铸造将Int值列转换为VARCHAR:

在您的查询中 铸造emp_card_no 至VARCHAR

select 
    case when status='R' then 'Resign'
    when status='A' then 'Active' end as status1, 
    [Program_name] + ' ' + CAST(emp_card_no AS VARCHAR(50)) as program_details,
* 
from GetEmployeeDetails
Where emp_name ='ABHAY ASHOK MANE'and STATUS= 'A' ORDER BY EMP_NAME

4

1

Kannan Kandasamy 9 年前

您可以使用concat进行内部转换。。

select case when status='R' then 'Resign'
when status='A' then 'Active' end as status1, 
 concat(Program_name, ' ', emp_card_no) as program_details,
* from GetEmployeeDetails
Where emp_name ='ABHAY ASHOK MANE'and STATUS= 'A' ORDER BY EMP_NAME

5

0

maulik kansara 9 年前

可以对字符串类型值进行列组合。因此,如果没有,则必须将列转换为varchar数据类型。

以下是您的解决方案 案例陈述的一个改进。

select 
case status 
   when 'R' 
      then 'Resign'
   when 'A' 
      then 'Active' 
end as status1, 
Program_name + ' ' + convert(varchar(50),emp_card_no) as program_details,
* 
from 
GetEmployeeDetails
where 
emp_name = 'ABHAY ASHOK MANE'
and STATUS = 'A' 
order by
EMP_NAME