deeejay

psql - Convert rows to single comma or pipe-delimited row

Aug 11th, 2015
513
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CAST(SUBSTRING(ARRAY_TO_STRING(ARRAY_AGG(hf_index_flag), ' | ') FROM '1') AS SMALLINT) AS hf_index_flag
  2.  
  3. Explanation broken apart below:
  4.  
  5. ARRAY_AGG(hf_index_flag) = Converts multiple rows to a single comma-delimited value
  6. Example:
  7. hf_index_flag       to      hf_index_flag
  8. ===                 ===
  9. 0                   0,0,1
  10. 0
  11. 1
  12.  
  13. ARRAY_TO_STRING(ARRAY_AGG(hf_index_flag), ' | ') = Converts comma-delimited to pipe-delimited (with space)
  14. Example:
  15. hf_index_flag       to      hf_index_flag
  16. ===                 ===
  17. 0,0,1                   0 | 0 | 1
  18.  
  19. SUBSTRING(ARRAY_TO_STRING(ARRAY_AGG(hf_index_flag), ' | ') FROM '1') = Removes other values other than 1
  20. Example:
  21. hf_index_flag       to      hf_index_flag
  22. ===                 ===
  23. 0 | 0 | 1               1
Advertisement
Add Comment
Please, Sign In to add comment