CAST(SUBSTRING(ARRAY_TO_STRING(ARRAY_AGG(hf_index_flag), ' | ') FROM '1') AS SMALLINT) AS hf_index_flag
Explanation broken apart below:
ARRAY_AGG(hf_index_flag) = Converts multiple rows to a single comma-delimited value
Example:
hf_index_flag to hf_index_flag
=== ===
0 0,0,1
0
1
ARRAY_TO_STRING(ARRAY_AGG(hf_index_flag), ' | ') = Converts comma-delimited to pipe-delimited (with space)
Example:
hf_index_flag to hf_index_flag
=== ===
0,0,1 0 | 0 | 1
SUBSTRING(ARRAY_TO_STRING(ARRAY_AGG(hf_index_flag), ' | ') FROM '1') = Removes other values other than 1
Example:
hf_index_flag to hf_index_flag
=== ===
0 | 0 | 1 1
Comments