In MySQL the group_concat function is used to returns a string result with the concatenated non-NULL values from a group.
Example from MySQL:
Code: Select all
(mydbadmin@localhost) [jim]> select * from combine;
+--------+
| name |
+--------+
| raj |
| sekhar |
| nicely |
+--------+
3 rows in set (0.00 sec)
(mydbadmin@localhost) [jim]> select group_concat(name) from combine;
+--------------------+
| group_concat(name) |
+--------------------+
| raj,sekhar,nicely |
+--------------------+
1 row in set (0.00 sec)
One way to simulate this in Vertica is the following:
Code: Select all
dbadmin=> select * from combine;
name
--------
raj
sekhar
nicely
(3 rows)
dbadmin=> select MAX(DECODE(rn, 1, name)) ||
dbadmin-> NVL(MAX(DECODE(rn, 2, ',' || name)), '') ||
dbadmin-> NVL(MAX(DECODE(rn, 3, ',' || name)), '') ||
dbadmin-> NVL(MAX(DECODE(rn, 4, ',' || name)), '') ||
dbadmin-> NVL(MAX(DECODE(rn, 5, ',' || name)), '') ||
dbadmin-> NVL(MAX(DECODE(rn, 6, ',' || name)), '') ||
dbadmin-> NVL(MAX(DECODE(rn, 7, ',' || name)), '') ||
dbadmin-> NVL(MAX(DECODE(rn, 8, ',' || name)), '') ||
dbadmin-> NVL(MAX(DECODE(rn, 9, ',' || name)), '') ||
dbadmin-> NVL(MAX(DECODE(rn, 10, ',' || name)), '') group_concate
dbadmin-> from (select row_number() over() rn, name from combine) foo;
group_concate
-------------------
raj,sekhar,nicely
(1 row)
Have fun!