Question 89149 in PostgreSQL Question posted in
The official documentation can be found here.

SQL – If given value is matched then display column name in another column – Postgresql

This is my source data.

a  |b  |c  |d  |
---+---+---+---+
YES|YES|NO |YES|
NO |YES|NO |NO |
NO |NO |YES|YES|
YES|YES|YES|NO |

My expected output is

a  |b  |c  |d  |output  |
---+---+---+---+--------+
YES|YES|NO |YES|C       |
NO |YES|NO |NO |A |C |D |
NO |NO |YES|YES|A |B    |
YES|YES|YES|NO |D       |

The additional column output , will display column value based on ‘NO’. At Row-no 1 , the value ‘No’ from C , At row-no 2 No from A C, and D.
to get above result, i written below query

select  
    A,B,C,D
    ,concat_ws('|',nullif(A,'YES'),nullif(B,'YES'),nullif(C,'YES'),nullif(D,'YES')) as output
from 
    test.tab

But it is printing ‘NO’ value. But how to bring which column is having ‘NO’ value.

Tags:

2

Answers


  1. 0

    You could use CASE expressions instead of NULLIF:

    SELECT A, B, C, D,
       CONCAT_WS('|', CASE WHEN A = 'NO' THEN 'A' END,
                      CASE WHEN B = 'NO' THEN 'B' END,
                      CASE WHEN C = 'NO' THEN 'C' END,
                      CASE WHEN D = 'NO' THEN 'D' END) AS output
FROM test.tab;
    Login or Signup to reply.
  2. 0

    You can try below as well in postresql, though it’s not an ansi-sql.

    with source_data as (
  select 'YES' a, 'YES' b, 'NO' c, 'YES' d union all
  select 'NO', 'YES', 'NO', 'NO' union all
  select 'NO', 'NO', 'YES', 'YES' union all
  select 'YES', 'YES', 'YES', 'NO'
)
select *,
       (select string_agg(c[1], '|') 
          from regexp_matches(row_to_json(t)::text, '"(w+)":"NO"', 'g') c
       ) as output
  from source_data t

--Query results
+-----+-----+-----+-----+--------+
|  a  |  b  |  c  |  d  | output |
+-----+-----+-----+-----+--------+
| YES | YES | NO  | YES | c      |
| NO  | YES | NO  | NO  | a|c|d  |
| NO  | NO  | YES | YES | a|b    |
| YES | YES | YES | NO  | d      |
+-----+-----+-----+-----+--------+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search