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

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

CodePudding user response：

SQL is expressive enough to solve such case without UDF.

Here example of solving it using

CodePudding user response：

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