LISTAGG() function in Snowflake?

Explained: LISTAGG() function in Snowflake?

by

Are you looking to find how to use the LISTAGG() function within the Snowflake cloud data warehouse or maybe you are looking for a solution, how to use the LISTAGG function in Snowflake? If you are looking for any of these problem solutions then you have landed on the correct page. I will also show you what and how to use the LISTAGG() function. I will explain the LISTAGG() function by taking a practical exscarpe eleganti senza lacci converse lugged beige babyphone mit alexa verbinden Purchase college team jerseys at a discounted price and of high quality gepunktete strumpfhose μπουφαν γυναικειο speed x cordura nfl jersey sales tapis de reception gonflable raptor ćwiczenia na rowerze años 20 hombre disfraz dänisches bettenlager lounge set cheap jordan 4 ćwiczenia na rowerze mascarilla pelo sebastian yeezy sneakersample. So don’t waste time, let’s start a step-by-step guide to understanding what is the LISTAGG() expression.mindfulmusclellc.com bežecké tenisky lucianosousa.net propiedadesenrepublicadominicana.com greensandseeds.com שולחנות מתקפלים justineanweiler.com Mens VANS 2020 team easy on tøj til salg jorgensenfarmsinc.com mindfulmusclellc.com haynesplumbingllc.com team easy on tøj til salg jorgensenfarmsinc.com greensandseeds.com

What is LISTAGG() Function?

LISTAGG() function helps to get the concatenated input values, by the delimiter. For example, we are having values some column values like 1 to n, then after applying the function, the result would be 1 to n, but there will be a delimiter( ex: 1,2,3..).

How can we use the LISTAGG() Function?

LISTAGG() function is used to return the concatenated input values, separated by the delimiter string.

  • This function supports DISTINCT.
  • If you are not specifying WITHIN_GROUP, then the order of the data within each list is unpredictable.
  • If you are specifying DISTINCT and WITHIN_GROUP, then both refer to the same column.

What is the syntax of the LISTAGG() function in Snowflake?

-- Syntax : 

-- Aggregate 
LISTAGG ( [Distinct]  Expression [, delimiter] ) [ WITHIN GROUP ( order_by_clause	) ]

-- Window
LISTAGG ( [Distinct]  Expression-1 [, delimiter] )
[ WITHIN GROUP ( order_by_clause ) ]
OVER ( [ PARTITION BY Expression-2 ] )

LISTAGG() Argument Details :

Expression-1String value or Cast to String
DelimiterString value
Expression-2Required column name
Order_by_clauseOrder by Column Name
Table 1: ListAgg Argument List

Expression-1:

Expression determines the values to be put into the list. The expression should be evaluated to string or data type that can be cast to string.

Delimiter:

An Expression or a string that evaluates to a string. This is usually a single-character string. The string should be surrounded by a single quote.

Expression-2:

This expression is used to group the rows in partitions.

Order_by_clause:

An expression that determines the order of values in a list.

Returns:

It returns a string that includes all the non-NULL input values, separated by the delimiter.

Example of LISTAGG() function in Snowflake:

Firstly, let’s create a table and insert some sample data.

create or replace TABLE EMP (
ID NUMBER(38,0),                                                                         
NAME VARCHAR(30) COMMENT 'NAME OF THE EMPLOYEE',                                                                         
INFO VARCHAR(20) DEFAULT 'NOT-NULL'  
)


Insert into demo.public.emp(id,name) values((1,'Ram'),(2,'RAJU'),(3,'Sameer'))



select * from demo.public.emp;

+----+--------+----------+
| ID | NAME   | INFO     |
|----+--------+----------|
|  1 | Ram    | NOT-NULL |
|  2 | RAJU   | NOT-NULL |
|  3 | Sameer | NOT-NULL |
+----+--------+----------+

How to concatenate data with space using the LISTAGG() function with an example?

select listagg(name, ' ')
from demo.public.emp where id > 1;

+--------------------+
| LISTAGG(NAME, ' ') |
|--------------------|
| RAJU Sameer        |
+--------------------+

How to concatenate data with ‘||’ using the LISTAGG() function with an example?

select listagg(name, ' || ')
from demo.public.emp where id > 1;

+-----------------------+
| LISTAGG(NAME, ' || ') |
|-----------------------|
| RAJU || Sameer        |
+-----------------------+

How to concatenate data with ‘|’ using the LISTAGG() function with an example?

select listagg(distinct name, ' | ')
from demo.public.emp where id > 1;

+-------------------------------+
| LISTAGG(DISTINCT NAME, ' | ') |
|-------------------------------|
| RAJU | Sameer                 |
+-------------------------------+

Full Example of LISTAGG function in Snowflake?

-- Create a table

create or replace TABLE EMP1 (
	ID NUMBER(38,0),
	NAME VARCHAR(30),
	INFO VARCHAR(20),
);

-- Insert Data

insert into emp(id,name,amount) values 
(1,'Ram'),
(2,'RAJU'),
(3,'Sameer'),
(4,'Sai Kumar'),
(5,'Naveen Sai'),
(6,'Seshi Kumar'),
(7,'Rajiv Ram'),
(8,NULL)


-- Table Data

SELECT * from emp;
+----+-------------+----------+
| ID | NAME        | INFO     |
|----+-------------+----------|
|  1 | Ram         | NOT-NULL |
|  2 | RAJU        | NOT-NULL |
|  3 | Sameer      | NOT-NULL |
|  4 | Sai Kumar   | NOT-NULL |
|  5 | Naveen Sai  | NOT-NULL |
|  6 | Seshi Kumar | NOT-NULL |
|  7 | Rajiv Ram   | NOT-NULL |
|  8 | NULL        | NOT-NULL |
+----+-------------+----------+

-- Applying LISTAGG to the table data

select listagg(name,'|') as insert_ from emp;
+------------------------------------------------------------+
| INSERT_                                                    |
|------------------------------------------------------------|
| Ram|RAJU|Sameer|Sai Kumar|Naveen Sai|Seshi Kumar|Rajiv Ram |
+------------------------------------------------------------+

select listagg(name,',') as insert_ from emp;
+------------------------------------------------------------+
| INSERT_                                                    |
|------------------------------------------------------------|
| Ram,RAJU,Sameer,Sai Kumar,Naveen Sai,Seshi Kumar,Rajiv Ram |
+------------------------------------------------------------+

select listagg(id,',') as insert_ from emp;
+-----------------+
| INSERT_         |
|-----------------|
| 1,2,3,4,5,6,7,8 |
+-----------------+

When you should use LISTAGG() Function in Snowflake?

There are certain use case scenarios when it is recommended to use the LISTAGG function within the Snowflake cloud data warehouse which are as follows:

  • If we want to apply delimiter to a table data, then we can make use of this function as you have seen in examples.

Real World Use Case Scenarios for LISTAGG Function in Snowflake

  • In public bus, there are people, having tikets which are connected to the Trasport facilities.
  • A csv file will be having data followed by comma seperated.

LISTAGG() Snowflake Official Documentation Link

Final Thoughts

In this article, we have learned about LISTAGG() function and uses with the examples explained clearly. I have also covered different scenarios with a practical example that could be possible. I hope the information that was provided – helped in gaining the knowledge.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

Revanth Kumar