asked
Return CSV of Columns Containing a Value
Hi there, Is there a better more efficient way to rewrite the following CASE statement? --create test table create table tab1( col1 char(1), col2 char(1), col3 char(1)) --insert test data insert into tab1(col1,col2,col3) values('X',NULL,NULL) insert into tab1(col1,col2,col3) values(NULL,'X',NULL) insert into tab1(col1,col2,col3) values(NULL,NULL,'X') insert into tab1(col1,col2,col3) values('X','X',NULL) insert into tab1(col1,col2,col3) values('X',NULL,'X') --query table select case when col1 = 'X' and col2 is null and col3 is null then 'col1' when col1 is null and col2 = 'X' and col3 is null then 'col2' when col1 is null and col2 is null and col3 = 'X' then 'col3' when col1 = 'X' and col2 = 'X' and col3 is null then 'col1,col2' when col1 = 'X' and col2 = 'X' and col3 = 'X' then 'col1,col2,col3' when col1 is null and col2 is null and col3 = 'X' then 'col3' when col1 = 'X' and col2 is null and col3 = 'X' then 'col1,col3' when col1 is null and col2 = 'X' and col3 = 'X' then 'col2,col3' end as cols from tab1 Basically I have a table with a large number of columns, from which I need to produce a csv output of the column name for each column that contains an 'X' value. TIA
I might switch it up so there is only one case condition per column: ;with delimited_list as ( select case when col1 = 'X' then 'col1,' else '' end + case when col2 = 'X' then 'col2,' else '' end + case when col3 = 'X' then 'col3,' else '' end as csv from tab1 ) select substring(csv, 1, len(csv) - 1) from delimited_list where len(csv) > 0 It still requires coding for every column, so it will miss new columns. If you wanted to code up something with dynamic SQL, you could probably put something together that picks up new columns as they are added.
15 People are following this question.
