CODEWITHSUNDEEP
sqlpostgresqlselectmetadata
Willi Mentzel
Willi Mentzel

Reputation: 29924

How to dynamically query for a column name?

Consider the following piece of SQL code for a PostgreSQL DBMS (9.3):

SELECT 
    column_name, (SELECT count(*) FROM _TABLE_NAME_ WHERE column_name = 'X')
FROM 
    information_schema.columns 
WHERE 
    table_schema = '_SCHEMA_NAME_'
AND 
    table_name = '_TABLE_NAME_';

I want to use the retrieved column_names from the outer query in the inner query as column names (meta). Is there a way to do this in one SELECT statement (multiple inner SELECT statements are ok too)?

Example:

enter image description here

Upvotes: 2

Views: 319

Answers (2)

Willi Mentzel
Willi Mentzel

Reputation: 29924

This is not my work. I asked a colleague and after Jakub Kania's idea with json_each_text and row_to_json he came to the following solution which exactly answers my question.

SELECT 
    k, sum((v = 'X')::INT)
FROM 
(
    SELECT 
        (json_each_text(row_to_json(_TABLE_NAME_WITHOUT_SCHEMA_))).KEY AS k, 
        (json_each_text(row_to_json(_TABLE_NAME_WITHOUT_SCHEMA_))).VALUE AS v
    FROM 
        _TABLE_NAME_
) AS tmp
GROUP BY k;

Upvotes: 1

Jakub Kania
Jakub Kania

Reputation: 16497

You don't need meta tables, you need an unpivot query. With a reasonably recent pg you can achieve it with JSON functions like this:

SELECT key
      ,count(*) FILTER (WHERE value = 'X') 
FROM  _TABLE_NAME_
      ,json_each_text(row_to_json( _TABLE_NAME_)) AS unpivot
GROUP BY key;

Upvotes: 2

Related Questions