Converting the header row to a column PostgreSQL-CodePudding

I am new to PostgreSQL. I have a table with three columns as mentioned below and i want to convert the header row to a column with the corresponding values in another column and finally a new column that checks if the value is missing or not.

ID   NAME   EMAIL            SALARY
1    John   ''               1000
2    Sam    [email protected]   6000
3    Tom    [email protected]   9000
4    Bob    [email protected]    500
5    Lari   [email protected]   3000

The expected output for each ID:

ID   fields      Value            Verified
 1    NAME        John              Yes
      EMAIL        ''               No
      SALARY      1000              Yes

 2    NAME        Sam               Yes
      EMAIL      [email protected]     Yes
      SALARY      6000              Yes

CodePudding user response：

Another option is to use a CROSS JOIN with a VALUES clause that transposes the columns into rows:

select t.id, 
       u.*,
       case 
         when u.value = '' or u.value is null then 'No'
         else 'Yes'
       end as verified
from the_table t
  cross join lateral (
     values ('NAME', name), ('EMAIL', email), ('SALARY', salary::text)
  ) as u(column_name, value)
order by t.id, u.column_name

This is shorter to write and easier to maintain, but I have seen the UNION ALL approach from Thorsten Kettner to be faster in some situations.

CodePudding user response：

What you call the header row is no row at all, but just the column names. You want one row per ID and column. While this is something I'd rather do in the app and not in SQL, it is possible of course. You need one query per column. Glue their results together with UNION ALL.

select
  id, 
  'NAME' as fields,
  name as value,
  case when name is null or name = '' then 'No' else 'Yes' as verified
from mytable
union all
select
  id, 
  'EMAIL' as fields,
  email as value,
  case when email is null or email = '' then 'No' else 'Yes' as verified
from mytable
union all
select
  id, 
  'SALARY' as fields,
  cast(salary as varchar) as value,
  case when salary is null then 'No' else 'Yes' as verified
from mytable
order by id, fields;

But as mentioned, it would be better just to select * from mytable and care about the presentation of the data in your app. This is probably much faster, too.