sqlsql-serverpowerbicolumn-alias

How to reuse column alias in another column within same select statement?


select pd.id
       ,[value 1] = Case
                    ----
                    End
       ,[value 2] = Case when [value 1] in ('test1', 'test2') Then 0
                    else [value 1] End

I am getting as invalid column when I try to use value 1 in value 2. Is there a possibility to use calculated [value 1] to be used in [value 2]

My current solution is to create a separate sp or view for value 1 but that's not what I want to do.


Solution

  • In SQL Server, you can use APPLY to define column aliases in the FROM clause -- a good place, in my opinion:

    select pd.id v.value_1,
           (Case when value_1 in ('test1', 'test2') Then '0'
                 else value_1
            End) as value_2
    from pd cross apply
         (values ( case . . . )
         ) v(value_1);
    

    Notes: