Get column names in a list aggregate when the row values are non zero

user_18162558 0
Viewed 49

Hi I'm experiencing problems in trying to get column names from a table which has non zero row values. Take the following sample data :

create table your_table
(
  XUUID VARCHAR(100), 
  COUNT1 INT,
  COUNT2 INT,
  COUNT3 INT,
  COUNT4 INT
); 

INSERT INTO your_table values 
('id1', 2, 3, 0, 0),
('id2', 0, 0, 1, 0),
('id3', 0, 0, 0, 0),
('id4', 3, 0, 0, 0)

I would like the result to be of the format :

xuuid |  non_zero_col_agg    |
------|----------------------|
id1   | ['count1', 'count2'] |
id2   | ['count3']           |
id3   | []                   |
id4   | ['count1']           |

I tried to do this with the following javascript function :

create or replace function labels(count1 INT, count2 INT, count3 INT, count4 INT)
    returns listagg
    language javascript 
    strict
    as
    $$
    var res = [];
    var dict = {
        'count1' : COUNT1,
        'count2' : COUNT2,
        'count3' : COUNT3,
        'count4' : COUNT4
    };
    for (var key in dictionary){
        if (dict[key] > 0) {
            res.push(key)
        }
    };
    return(res);
    $$
;

But it returned the following error : SQL compilation error: Unsupported data type 'LISTAGG'.

Is there a way to solve this problem? The solution can be as a function or not as a function, I choose to do it as a function because I didn't know of a way to do it with regular SQL.

Thanks

snowflake-cloud-data-platform sql
2 Answers

SQL is expressive enough to solve such case without UDF.

Here example of solving it using OBJECT_CONSTRUCT. This solution is dynamic and it could accept any number of "countX" columns present on the row level:

WITH cte AS (
  SELECT xuuid, OBJECT_CONSTRUCT(*) AS c
  FROM your_table
)
SELECT XUUID, ARRAY_AGG(CASE WHEN s.KEY LIKE 'COUNT%' 
                             AND TRY_CAST(s.VALUE::STRING AS INT) > 0 THEN s.key END)
FROM cte
,LATERAL FLATTEN(input => c) s
GROUP BY XUUID
ORDER BY XUUID;

Output:

enter image description here

user_50708790

Here is a solution in SQL

create table your_table
(
  XUUID VARCHAR(100), 
  COUNT1 INT,
  COUNT2 INT,
  COUNT3 INT,
  COUNT4 INT
); 

INSERT INTO your_table values 
('id1', 2, 3, 0, 0),
('id2', 0, 0, 1, 0),
('id3', 0, 0, 0, 0),
('id4', 3, 0, 0, 0)  

SELECT
XUUID,
CONCAT('[ ',
CASE COUNT1 WHEN 0 
THEN '' ELSE 'COUNT1 ' END,
CASE COUNT2 WHEN 0
THEN '' ELSE 'COUNT2 ' END,
CASE COUNT3 WHEN 0
THEN '' ELSE 'COUNT3 ' END,
CASE COUNT4 WHEN 0
THEN '' ELSE 'COUNT4 ' END,
']')cols
FROM your_table;

XUUID | cols             
:---- | :----------------
id1   | [ COUNT1 COUNT2 ]
id2   | [ COUNT3 ]       
id3   | [ ]              
id4   | [ COUNT1 ]

db<>fiddle here

Admin1
Related