user3684314 user3684314 - 3 months ago 16
SQL Question

Postgres/pgadmin3 - Selecting all columns equal to true for a given row

I'm not sure exactly how to go about doing this, but I have to select all columns in a given row in which the value is equal to the boolean TRUE.

i.e.

Columns: | X | Y | Z | A |
0 | TRUE | FALSE | TRUE | TRUE |


In this case, I would need an SQL statement that would return:

Columns: | X | Z | A |
0 | TRUE | TRUE | TRUE |


I'm doing this across a table whose size will not change (it's static essentially) with about 250 columns and 220 rows.

Eventually, I'm going to need a statement which returns the names of the columns where TRUE, basically:

0 | X | Z | A |


Any help would be greatly appreciated!

EDIT 1

Based on Nicarus' solution below, I came up with the following:

WITH i (A, B, C)
AS (
SELECT attributes.A, B, C
FROM (attributes JOIN contexts ON attributes.A = contexts.A)
WHERE context_full_name = 'Print book'
),
i_sub AS (
SELECT
A,
UNNEST(ARRAY['B', 'C'])
AS col_name,
UNNEST(ARRAY[B, C])
AS col_value
FROM i)
SELECT STRING_AGG(col_name, ',') AS true_col_names INTO temporary_table
FROM i_sub WHERE col_value = TRUE GROUP BY A;

SELECT * FROM temporary_table;


However, I am returned a result that is false with the final select statement...

I double-checked using:

SELECT *
FROM (attributes JOIN contexts ON attributes.attribute_id = contexts.attribute_id)
WHERE context_full_name = 'Print book';


And the column is definitely false...

Did I mess something up?

EDIT 2

So the query in EDIT 1 is trying to achieve the following; changing this:

Columns: | X | Y | Z | A |
0 | TRUE | FALSE | TRUE | TRUE |
1 | TRUE | TRUE | TRUE | FALSE|
2 | FALSE| FALSE | TRUE | FALSE|


In this case, I would need an SQL statement that would return:

Columns: | X | Z | A |
0 | TRUE | TRUE | TRUE |


Instead, it is returning all columns with a true value anywhere:

Columns: | X | Y | Z | A |
0 | TRUE | TRUE | TRUE | TRUE |


MY IMPLEMENTATION

This is my no means the most efficient, but it worked for what I was looking to accomplish:

SELECT attributes.attribute_id, context_full_name, A, B, C, D
INTO TEMP j
FROM (attributes JOIN contexts ON attributes.attribute_id = contexts.attribute_id)
WHERE contexts.context_full_name = 'Print book' LIMIT 1;

WITH i_sub AS (
SELECT
attribute_id,
context_full_name,
UNNEST(ARRAY[A, B, C, D]) AS col_value
FROM j)
SELECT ROW_NUMBER() OVER () as rn, *
INTO TEMP temporary_table
FROM i_sub;

SELECT context_full_name, attribute_id, temporary_table.rn, temporary_table.col_value, attribute_titles.attribute_name_column, attribute_titles.attribute_names
INTO TEMP result
FROM
(temporary_table JOIN attribute_titles -- attribute titles is a table I created which lists the column headers in the same order as they are in the attributes table, so that the row number on the temporary_table equals the attribute_titles column "attribute_name_id".
ON temporary_table.rn = attribute_titles.attribute_name_id) WHERE col_value = TRUE;

SELECT * FROM result; -- This prints the list with the attribute_id, context_full_name, the column value where TRUE (to check to make sure it worked), the column names shown in attribute_titles, and the attribute names (plaintext versions). This table can be further manipulated as necessary.

Answer

I'm not 100% certain of your use case; however, you can return a "list" of the column names where their value is TRUE. This is only one way to do so.

Here I "un-pivot" the data, filter out any FALSE records, then concatenate the column names to return a single record per row. You may also decide to leave the data more normalized (not concatenating the column names)

WITH mytable (row_id,X,Y,Z,A) AS
(
VALUES
    (1,TRUE,TRUE,TRUE,FALSE),
    (2,TRUE,FALSE,TRUE,FALSE),
    (3,FALSE,TRUE,TRUE,TRUE)
),

mycte AS
(
SELECT
    row_id,
    UNNEST(ARRAY['X','Y','Z','A']) AS col_name,
    UNNEST(ARRAY[X,Y,Z,A]) AS col_value
FROM
    mytable
)

SELECT
    row_id,
    STRING_AGG(col_name,'|') AS true_col_names
FROM
    mycte
WHERE
    col_value
GROUP BY
    row_id;

You state you are still having issues, though I am not clear on what they are.

I used your query (with my test data) and I get this:

DROP TABLE IF EXISTS temporary_table;

WITH i (A,B,C) AS
(
VALUES
    (TRUE,TRUE,FALSE),
    (FALSE,TRUE,FALSE),
    (TRUE,TRUE,TRUE)
),

i_sub AS (
SELECT 
    A,
    UNNEST(ARRAY['B', 'C']) 
        AS col_name,
    UNNEST(ARRAY[B, C]) 
        AS col_value
FROM i)

SELECT
    STRING_AGG(col_name, ',') AS true_col_names,
    STRING_AGG(DISTINCT col_name, ',') AS true_col_names_unique --only unique col_names
INTO
    temporary_table
FROM
    i_sub
WHERE
    col_value;

SELECT * FROM temporary_table;

Output:

enter image description here

Comments