SQL Server How to Alias SQL column name with condition

mmvthczy  于 2023-05-28  发布在  其他
关注(0)|答案(2)|浏览(205)

I am using a stored procedure (SQL Server) from get value where @classtype is a parameter, I want to when classtype = p then classType value come from table but column name English or Other base on classtype, I am trying below code for get data but get error for:
AS not supported.

select
  col
  , col1
  , CASE WHEN @classtype ='P' THEN classType AS 'English' ELSE classType as 'Other' END, col4
from my_table

Thanks.

7z5jn7bk

7z5jn7bk1#

You can use an IF statement to return a different resultset.

IF @ClassType = 'P' BEGIN
    SELECT
        col
        , col1
        , classType AS English
        , col4
    FROM my_table;
END; ELSE BEGIN
    SELECT
        col
        , col1
        , classType AS Other
        , col4
    FROM my_table;
END;

Or dynamic SQL

DECLARE @Sql nvarchar(max) = 
'SELECT
    col
    , col1
    , classType AS ' + CASE WHEN @ClassType = 'P' THEN 'English' ELSE 'Other' END + '
    , col4
FROM my_table;';

EXEC sp_execute @Sql;
mkshixfv

mkshixfv2#

You can use dynamic SQL

DECLARE @classtype CHAR(1);
DECLARE @alias CHAR(10);
DECLARE @sql CHAR(1000);
SET @classtype = 'P';
IF (@classtype = 'P')
BEGIN
    SET @alias = 'English';
END
ELSE
BEGIN
    SET @alias = 'Other';
END
SET @sql = 'select
    col
    , col1
    , classType as ' + @alias +
    ', col4
from my_table';

EXEC (@sql);

相关问题