Combine Multiple ROWS into single row

It also works using plain vanilla SQL - with the limitation that you have to hard-wire the maximum number of elements in the list;

By first pivoting the list elements, so that you have one column per possible "name" from the initial lis.,

And then concatenating them into one column, using an IFNULL() function for each element.

While doing that:

For each non-first column, you evaluate whether any predecessor column of the current one is not null, by using a COALESCE() on all predecessor columns and evaluating whether that function's result is null, and concatenate an empty string or a comma with the current column's content accordingly; and if the current column is null, the whole concatenation becomes null; an IFNULL() function around it makes sure you end up with an empty string in this case.

Here's a script that builds the whole thing step by step, using a global table expression (WITH clause) - by un-commenting the line "union all select 2,'Australia' " , you can test the change in the behaviour on setting or not setting commas.

Happy playing - marco

with
item_list(id,name) as (
          select 1,'India'
union all select 2,'Australia'
union all select 3,'Africa'
)
select * from item_list;

with
item_list(id,name) as (
          select 1,'India'
union all select 2,'Australia'
union all select 3,'Africa'
)
,not_yet_pivoted (name_first,name_second,name_third) as (
select
 case id when 1 then 'India' end
,case id when 2 then 'Australia' end
,case id when 3 then 'Africa' end from item_list
)
select * from not_yet_pivoted;

with
item_list(id,name) as (
          select 1,'India'
union all select 2,'Australia'
union all select 3,'Africa'
)
,pivoted (name_first,name_second,name_third) as (
select
 max(case id when 1 then 'India' end)
,max(case id when 2 then 'Australia' end)
,max(case id when 3 then 'Africa' end)
from item_list
)
select * from pivoted;

with 
item_list(id,name) as (
          select 1,'India'
--union all select 2,'Australia'
union all select 3,'Africa'
)
,pivoted (name_first,name_second,name_third) as (
select
 max(case id when 1 then 'India' end)
,max(case id when 2 then 'Australia' end)
,max(case id when 3 then 'Africa' end) 
from item_list
)
,the_flexible_list_table (the_list_column) as (
select
  ifnull(name_first,'')
||ifnull(
     case when coalesce(name_first) is not null then ',' else '' end
   ||name_second
  ,''
  )
||ifnull(
     case when coalesce(name_first,name_second) is not null then ',' else '' end
   ||name_third
  ,''
  )
from pivoted
)
select * from the_flexible_list_table
;

Hi Abhishek,

Thank you for sharing the step by step process for the installation.

I did follow the same steps and got the below error:

ERROR 3457:  Function group_contact

[dbadmin@dilipvertica32 packages]$ cd ~

[dbadmin@dilipvertica32 ~]$ pwd

/home/dbadmin

[dbadmin@dilipvertica32 ~]$ cp /opt/vertica/packages/Vertica-Extension-Packages-master.zip Vertica-Extension-Packages-master.zip

[dbadmin@dilipvertica32 ~]$ ll

total 70864

drwxr-xr-x. 3 dbadmin verticadba     4096 Feb 21 16:33 catalog

drwxr-xr-x. 3 dbadmin verticadba     4096 Feb 21 16:33 data

-rwxr-xr-x. 1 dbadmin verticadba 72553633 Jun 15 01:50 Vertica-Extension-Packages-master.zip

[dbadmin@dilipvertica32 ~]$ unzip Vertica-Extension-Packages-master.zip

[dbadmin@dilipvertica32 ~]$ pwd

/home/dbadmin

[dbadmin@dilipvertica32 ~]$ chmod 777 Vertica-Extension-Packages-master

[dbadmin@dilipvertica32 ~]$ ll

total 70868

drwxr-xr-x.  3 dbadmin verticadba     4096 Feb 21 16:33 catalog

drwxr-xr-x.  3 dbadmin verticadba     4096 Feb 21 16:33 data

drwxrwxrwx. 20 dbadmin verticadba     4096 Feb 26  2014 Vertica-Extension-Packag

-rwxr-xr-x.  1 dbadmin verticadba 72553633 Jun 15 01:50 Vertica-Extension-Packag

[dbadmin@dilipvertica32 ~]$ cd Vertica-Extension-Packages-master/strings_package/

[dbadmin@dilipvertica32 strings_package]$ make

g++ -g -D HAVE_LONG_LONG_INT_64 -c -I ../include -Wall -Wno-unused-value -fPIC -I /opt/vertica/sdk/include -I /home/dbadmin/Vertica-Extension-Packages-master/strings_package/src/third-party/include -O3 -DNDEBUG /opt/vertica/sdk/include/Vertica.cpp -o build/Vertica.o

g++ -g -D HAVE_LONG_LONG_INT_64 -c -I ../include -Wall -Wno-unused-value -fPIC -I /opt/vertica/sdk/include -I /home/dbadmin/Vertica-Extension-Packages-master/strings_package/src/third-party/include -O3 -DNDEBUG src/EditDist.cpp -o build/EditDist.o

g++ -g -D HAVE_LONG_LONG_INT_64 -c -I ../include -Wall -Wno-unused-value -fPIC -I /opt/vertica/sdk/include -I /home/dbadmin/Vertica-Extension-Packages-master/strings_package/src/third-party/include -O3 -DNDEBUG src/PorterStemmer.cpp -o build/PorterStemmer.o

src/PorterStemmer.cpp: In function ‘void step5(stemmer*)’:

src/PorterStemmer.cpp:375: warning: suggest parentheses around ‘&&’ within ‘||’

g++ -g -D HAVE_LONG_LONG_INT_64 -c -I ../include -Wall -Wno-unused-value -fPIC -I /opt/vertica/sdk/include -I /home/dbadmin/Vertica-Extension-Packages-master/strings_package/src/third-party/include -O3 -DNDEBUG src/StringTokenizerDelim.cpp -o build/StringTokenizerDelim.o

g++ -g -D HAVE_LONG_LONG_INT_64 -c -I ../include -Wall -Wno-unused-value -fPIC -I /opt/vertica/sdk/include -I /home/dbadmin/Vertica-Extension-Packages-master/strings_package/src/third-party/include -O3 -DNDEBUG src/NGrams.cpp -o build/NGrams.o

g++ -g -D HAVE_LONG_LONG_INT_64 -c -I ../include -Wall -Wno-unused-value -fPIC -I /opt/vertica/sdk/include -I /home/dbadmin/Vertica-Extension-Packages-master/strings_package/src/third-party/include -O3 -DNDEBUG src/StringTokenizer.cpp -o build/StringTokenizer.o

g++ -g -D HAVE_LONG_LONG_INT_64 -c -I ../include -Wall -Wno-unused-value -fPIC -I /opt/vertica/sdk/include -I /home/dbadmin/Vertica-Extension-Packages-master/strings_package/src/third-party/include -O3 -DNDEBUG src/WordCount.cpp -o build/WordCount.o

g++ -g -D HAVE_LONG_LONG_INT_64 -c -I ../include -Wall -Wno-unused-value -fPIC -I /opt/vertica/sdk/include -I /home/dbadmin/Vertica-Extension-Packages-master/strings_package/src/third-party/include -O3 -DNDEBUG src/AnagramLib.cpp -o build/AnagramLib.o

g++ -g -D HAVE_LONG_LONG_INT_64 -c -I ../include -Wall -Wno-unused-value -fPIC -I /opt/vertica/sdk/include -I /home/dbadmin/Vertica-Extension-Packages-master/strings_package/src/third-party/include -O3 -DNDEBUG src/GroupConcat.cpp -o build/GroupConcat.o

mkdir -p lib

g++ -shared -o lib/StringsLib.so build/Vertica.o build/EditDist.o build/PorterStemmer.o build/StringTokenizerDelim.o build/NGrams.o build/StringTokenizer.o build/WordCount.o build/AnagramLib.o build/GroupConcat.o

[dbadmin@dilipvertica32 strings_package]$ ls

build  ddl  doc  examples  lib  LICENSE.txt  Makefile  README.txt  src  test-data

dbadmin@dilipvertica32 strings_package]$ vsql

Password:

Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands

       \g or terminate with semicolon to execute query

       \q to quit

dbadmin=> \i /home/dbadmin/Vertica-Extension-Packages-master/strings_package/ddl/install.sql;

              version

------------------------------------

 Vertica Analytic Database v7.1.1-0

(1 row)

CREATE LIBRARY

CREATE FUNCTION

CREATE FUNCTION

CREATE TRANSFORM FUNCTION

CREATE TRANSFORM FUNCTION

CREATE TRANSFORM FUNCTION

CREATE TRANSFORM FUNCTION

CREATE TRANSFORM FUNCTION

CREATE TRANSFORM FUNCTION

CREATE FUNCTION

CREATE TRANSFORM FUNCTION

CREATE TRANSFORM FUNCTION

dilip=> select col1,group_contact(col2) over(partition by col1) from test_cont_group;

ERROR 3457:  Function group_contact(varchar) does not exist, or permission is denied for group_contact(varchar)

HINT:  No function matches the given name and argument types. You may need to add explicit type casts

dilip=> \d test_cont_group

                                         List of Fields by Tables

 Schema |      Table      | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key

--------+-----------------+--------+--------------+------+---------+----------+-------------+-------------

 public | test_cont_group | col1   | varchar(100) |  100 |         | f        | f           |

 public | test_cont_group | col2   | varchar(100) |  100 |         | f        | f           |

(2 rows)

Could you please help me?

Thanks much in advance.

Thanks & Regards

Dilip

Hi Dilip,

    Function name is misspelt in your query. function name is group_concat.

-Regards,

 Sruthi

Cant I use it on multiple columns. 
I tried using it on multiple column . It gave error 

 Cannot specify more than one user-defined transform function in the SELECT list

Is there any way out to apply ',' concatenation on multiple columns

HI ,

I am not sure how Lead and Lag will work in my case.

 Req is like  I have columns:

ID,NAME,DATE ,STATUS,PRIORITY_ORDER

and I have to apply group concatination on NAME,DATE ,STATUS,PRIORITY_ORDER based on ID.

I tried group_concat. But it allows a single column concatination at a time.

Is there any way out in vertica to deal with it.

Example using lead function :

dbadmin=> drop table foo;
DROP TABLE
dbadmin=> create table foo (id int ,contry varchar(20));
CREATE TABLE
dbadmin=> insert into foo values(1,'India');
OUTPUT
--------
1
(1 row)

dbadmin=> insert into foo values(2,'Australia');
OUTPUT
--------
1
(1 row)

dbadmin=> insert into foo values(3,'Africa');
OUTPUT
--------
1
(1 row)

dbadmin=> commit;
COMMIT
dbadmin=> SELECT India||','||Australia||','||Africa as "Name"
dbadmin-> FROM
dbadmin-> ( SELECT id,
dbadmin(> lead(contry,0) over ( order by id ) AS "India",
dbadmin(> lead(contry,1) over (order by id) AS "Australia",
dbadmin(> lead(contry,2) over (order by id) AS "Africa",
dbadmin(> row_number() over () AS rn
dbadmin(> FROM foo) AS foo_int
dbadmin-> WHERE rn =1;
Name
------------------------
India,Australia,Africa
(1 row)

dbadmin=>

Great !!

This can solve my problem.

I can apply Lead and LAG on as many column I want.

Thanks a lot. 

Ok Good 

Hi,

  A small tweak to the source code (which you have) would let group_concat support multiple columns.

-Ben

Hi Ben,

I am also trying to acheive the same but without lag and led function. You mentioned it can be done with some tweak in code. Can you please let us  know what and where to change.

Thanks,

why not concat the multi-columns as one ?

select id, NAME||DATE||STATUS||PRIORITY_ORDER over()...

I loaded this function but users other than dbadmin are not able to execute. Is there some specific grant or something which is needed to run this?

@usao - Are you refering to the GROUP_CONCAT function? If so, here is an example of granting EXECUTE to me:

dbadmin=> GRANT EXECUTE ON TRANSFORM FUNCTION group_concat(Varchar) TO jim;
GRANT PRIVILEGE