当基于条件SQL服务器时,使用Case选择列

时间:2021-06-03 19:26:56

I want to select a column based on Case when based on a condition. It was working fine but I don't know how to return the proper alias Name for the selected Column.

当基于条件时,我想根据情况选择一个列。它运行良好,但我不知道如何返回所选列的适当别名。

For example.

为例。

If I=1 then
I should select "L.id" with ColumnAlias as LeadId 
else 
I should select "sl.id" with ColumnAlias as ServiceLeadId

My output should contain only one column with proper alias like the below.

我的输出应该只包含一个具有适当别名的列,如下所示。

If I=1

LeadId 
1
2
3
...
...

If I<>1

ServiceleadId
1001
1002
1003
...
...

I tried like the below

我试了如下图

select
CASE WHEN @i = 1 Then
                  L.Id AS LeadId
                 Else

                  sl.id AS ServiceLeadId
end
from table

but I got an error.

但是我犯了一个错误。

Please suggest me any ideas to achieve this.

请给我提一些实现这一目标的建议。

1 个解决方案

#1


3  

CASE returning results in 1 column so It can hold only 1 column name, for example:

CASE返回1列中的结果,因此它只能保存1列名称,例如:

SELECT CASE WHEN @i = 1 THEN L.Id ELSE sl.id END AS LeadId
FROM TblName

If you want to have 2 different column names you should have separate columns:

如果你想有两个不同的列名,你应该有不同的列名:

SELECT CASE WHEN @i = 1 THEN L.Id END AS LeadId,
       CASE WHEN @i <> 1 THEN sl.id END AS ServiceLeadId
FROM TblName

UPDATE

更新

As per your comment you can use IF instead of CASE, something like:

根据你的评论,你可以用IF代替CASE,比如:

DECLARE @i int = 1

IF (@i = 1)
BEGIN
    SELECT L.Id AS LeadId
    FROM TblName
END
ELSE
    SELECT sl.id AS ServiceLeadId
    FROM TblName

#1


3  

CASE returning results in 1 column so It can hold only 1 column name, for example:

CASE返回1列中的结果,因此它只能保存1列名称,例如:

SELECT CASE WHEN @i = 1 THEN L.Id ELSE sl.id END AS LeadId
FROM TblName

If you want to have 2 different column names you should have separate columns:

如果你想有两个不同的列名,你应该有不同的列名:

SELECT CASE WHEN @i = 1 THEN L.Id END AS LeadId,
       CASE WHEN @i <> 1 THEN sl.id END AS ServiceLeadId
FROM TblName

UPDATE

更新

As per your comment you can use IF instead of CASE, something like:

根据你的评论,你可以用IF代替CASE,比如:

DECLARE @i int = 1

IF (@i = 1)
BEGIN
    SELECT L.Id AS LeadId
    FROM TblName
END
ELSE
    SELECT sl.id AS ServiceLeadId
    FROM TblName