I have a table like so -

n0   n1  n2
232  12  211
323  45  5655

I want to produce this output -

n    Count
232  1
12   1 
211  1
323  1
45   1
5655 1

I can do this using this query in PostgreSQL.

select el, count(*)
from 
(
SELECT  n0, n1, n2 from npi
) n
cross join lateral
     (select v.el
      from (values (n0), (n1), (n2)) v(el)
     ) v
group by el

But when I try to run the same query in Snowflake, I get this error -

Invalid expression [CORRELATION(N.N0)] in VALUES clause

CodePudding user response：

Alaternative form using UNION ALL:

WITH cte(el) AS (
  SELECT n0 FROM npi
  UNION ALL SELECT n1 FROM npi
  UNION ALL SELECT n2 FROM npi
)
SELECT el, COUNT(*)
FROM cte
GROUP BY el;

Output:

EDIT:

A version that uses FLATTEN combined with ARRAY_CONSTRUCT:

SELECT value AS el, COUNT(*)
FROM(SELECT  n0, n1, n2 from npi) AS n
,TABLE(FLATTEN(input => array_construct(n.n0, n.n1, n.n2)))
GROUP BY value;