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