1

I was trying to do column values concatenation in Netezza and found following for sql-server

Concatenating Column Values into a Comma-Separated List

It doesn't work for Netezza though.

Is there any solution?

What I have tried was

SELECT LEFT(COLUMN_NAME, LENGTH(COLUMN_NAME) - 1)
FROM (
    SELECT COLUMN_NAME + ', '
    FROM information_schema.columns
    WHERE  table_name IN ('Employee')
    FOR XML PATH ('')
  ) c (COLUMN_NAME)

Thanks

Community
  • 1
  • 1
Shabar
  • 2,617
  • 11
  • 57
  • 98

1 Answers1

2

A group_concat UDF that does what you want is provided here, and will need to be installed in the database in which you would like to use it. The provided tar file has an install script that does this for you. The installation looks something like this:

$ ./install testdb
CREATE AGGREGATE
Created uda
Done
CREATE AGGREGATE
Created uda
Done

The usage would look something like this:

TESTDB.ADMIN(ADMIN)=> select * from test_gc order by col1, col2;
 COL1 | COL2
------+------
    1 | A
    1 | B
    1 | C
    2 | C
    2 | D
    2 | E
(6 rows)

TESTDB.ADMIN(ADMIN)=> select col1, group_concat(col2,',') from test_gc group by col1 order by col1;
 COL1 | GROUP_CONCAT
------+--------------
    1 | A,B,C
    2 | C,D,E
(2 rows)
ScottMcG
  • 3,867
  • 2
  • 12
  • 21
  • Is there a way to check `group_concat ` has been installed. Tried this `SELECT * FROM _V_FUNCTION where FUNCTION = 'group_concat'; ` But no result Aperantly for `SQLEXT.(functions) ` do not come under that table – Shabar Mar 16 '15 at 05:30