How do I use Column Names of table in CASE Statement to assign Values and create another column in QUERY

Tinu — Tue, 21 Apr 2026 08:02:28 GMT

Hello,

I did enough google before coming to this forum. I have table Employee with following column Names (there are 40 columns)

Employee_ID, Employee_Type, Employee_Tenure

I want to use these column names in CASE Statement as part of HIVE Query to assign value.

E.g.

Case when Column Name = 'Employee_Type' then 1 else 0

when Column Name = 'Employee_Tenure' then 2 else 0

end as Rank

I know I can "show columns in Employee" to get all column names. But I don't know how to refer output of this query in CASE statement

I also try to save results of Query into variable ( I could be wrong doing this )

set hivevar:col_names=(show columns in employee)

select '${hivevar:col_names}'

but I get below error : Error while processing statement: Variable substitution depth is deeper than 40 for expression

Any help appreciated. If my question is too naive or stupid - I apologize.

Re: How do I use Column Names of table in CASE Statement to assign Values and create another column in QUERY

marudhu_ramaiah — Fri, 18 Feb 2022 09:16:23 GMT

let table hwctest has 2 columns as below,

getting column name from metadata and stored in temp var,

beeline -u "jdbcurl" --showHeader=false --outputformat=csv2 -e "SELECT COLUMN_NAME FROM sys.COLUMNS_V2 c JOIN sys.TBLS a ON c.CD_ID=a.TBL_ID where a.TBL_NAME='hwctest'" | tr "\n" "," > tmp a=$(cat tmp)

Passing col names as variable,

beeline -u "jdbcurl" --hivevar colName=${a%?} ..... 0: jdbc:hive2://c2757-node3.coelab.cloudera.c> select case when array_contains(split('${colName}',','),'col1') then 1 else 0 . . . . . . . . . . . . . . . . . . . . . . .> end as type ..... INFO : Executing command(queryId=hive_20220218082727_46d036f4-7667-485a-a2c9-c5be158292d4): select case when array_contains(split('col1,col2',','),'col1') then 1 else 0 end as type +-------+ | type | +-------+ | 1 | +-------+

replace the value col1 in your case.

question Re: How do I use Column Names of table in CASE Statement to assign Values and create another column in QUERY in Support Questions

How do I use Column Names of table in CASE Statement to assign Values and create another column in QUERY

Re: How do I use Column Names of table in CASE Statement to assign Values and create another column in QUERY