Combine Multiple ROWS into single row

Rajashekar_1

Hi,
    I have table look like this
   Id | Name
    1 | India
    2 | Australia
    3 | Africa

Now, I want Output in a single row like... 
 
 Name
 India, Australia, Africa

Is there any function to get the above output?

Abhishek_Rana

Hi,

TO achieve this, Install the strings package that has a group_concat function and use it.

Step by Step instructions on installing the Strings package from github community:

1. The page https://github.com/vertica/Vertica-Extension-Packages has a Download Zip link. Through this, you can download Vertica-Extension-Packages-master.zip and copy it over to one of  Vertica nodes. 
2. unzip Vertica-Extension-Packages-master.zip 
3. cd Vertica-Extension-Packages-master/strings_package 

4.[dbadmin]$ make 


5. [dbadmin strings_package]$ ls 
build ddl doc examples lib LICENSE.txt Makefile README.txt src test-data 

6. [dbadmin 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 

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




Example that shows how to use the group_concat function :
--------------------------------------------------------

rpt=> select * from patients;


 insurer | insured


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


 BCBS | John


 Medicare | Jim


 BCBS | Sue


(3 rows)

rpt=> select insurer,group_concat(insured) over(partition by insurer) from patients ;


 insurer | list


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


 Medicare | Jim


 BCBS | John, Sue


(2 rows) 


Regards'
Abhishek

Rajashekar_1

Thank you for your reply,
i followed your steps, but while running below stmt
dbadmin=>\i /home/dbadmin/Downloads/Vertica-Extension-Packages-master/strings_package/ddl/install.sql; \i install.sql



i am getting following errors

vsql:install.sql:6: ROLLBACK 3861:  Library not found: StringsLib
vsql:install.sql:7: ROLLBACK 3861:  Library not found: StringsLib
vsql:install.sql:8: ROLLBACK 3861:  Library not found: StringsLib

Rajashekar_1

Thank you for your reply,
i followed your steps, but while running below stmt
dbadmin=>\i /home/dbadmin/Downloads/Vertica-Extension-Packages-master/strings_package/ddl/install.sql; 



i am getting following errors

vsql:install.sql:6: ROLLBACK 3861:  Library not found: StringsLib
vsql:install.sql:7: ROLLBACK 3861:  Library not found: StringsLib
vsql:install.sql:8: ROLLBACK 3861:  Library not found: StringsLib

marcothesane

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
;

DilipKumar

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

 

SruthiA

Hi Dilip,

 

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

 

-Regards,

 Sruthi

Hdk

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

eli_revach

Hi,
Lag &lead are netive analytic function in vertica , that you can simply used for this use case.

If you need i can share some examples.

Tnx

Hdk

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.

 

 

 

eli_revach

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=>

Hdk

Great !!

This can solve my problem.

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

Thanks a lot. 

eli_revach

 

Ok Good 

Ben_Vandiver

Hi,

 

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

 

-Ben

mt25

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,

 

YahaS

why not concat the multi-columns as one ?

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

usao

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?

Jim_Knicely

@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