sql - 将多行转换为单个数组

Question

我当前的代码返回single-row error

 SELECT TO_ARRAY(
SELECT 'o' || '.' || c.column_name FROM INFORMATION_SCHEMA.COLUMNS As c WHERE table_name = 'TABLE_NAME' AND  c.column_name NOT IN('COL_NAME'));

子查询将每一列作为一行返回。添加时LIMIT 1，我得到了几乎想要的结果。如何选择这些行并将它们添加到数组中？

运行查询的预期结果应该是：

SELECT o.col_name1, o.col_name2... o.col_nameN FROM table_name As o

标签： sqlarrayssubqueryconcatenationsnowflake-cloud-data-platform

Answer 1

如果你想要一个数组，我会推荐array_agg()：

SELECT ARRAY_AGG('o' || '.' || c.column_name)
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.table_name = 'TABLE_NAME' AND
      c.column_name NOT IN('COL_NAME'));