Overview of Oracle LISTAGG Function

Hello friends, welcome to Oracle new feature video series. In this video, we are going to see about Oracle list ag function and its enhancements done in various versions of Oracle, starting from Oracle 11g till 19c. First, let us understand what is this listag function. So this is an aggregate function which will help us to convert a row of values into a comma separated string. For example, suppose if you have a table which contains like department wise name, then using this listag function, we can either generate a list of comma separated names like this or we can generate something like department wise comma separated information. This is the listag function. function was introduced first in 11g And in the subsequent versions of Oracle in 12c and in 19c, there are a lot of enhancements done in this function. So in this video, we are going to see what is the base functionality introduced in 11g. And then we are going to see what are the enhancements done in 12c and in 19c. First, let us start with what is the base functionality introduced in 11g. Let us start with the syntax introduced in the 11g. So here is the simple syntax introduced in 11g. So what we need to use is, We just need to say list act. and whatever the column we want to convert into a comma separated list of information, then we need to specify the optional delimiter. Either we can say comma or any special character. And this is an optional parameter. If you are not giving this information, then by default null will be considered as the delimiter. That means all the information will be concatenated without any delimiter. Then we need to say the within group class. So the within group class will say how we want to sort the elements within the component. comma separated string. So we just need to say either order by that element or the column name ascending or descending. Let us take an example. So suppose if I want to convert this information into a department wise comma separated names, then I need to say select department number, then you say list ag, whatever the column you want to convert into comma separated list of information. So in this case, I'm using comma as the delimiter. So you can use any special character here. And then we need to say. say within group order by employee name. That's why you can see all the employees working in department 10 are sorted based on the ascending order and then concatenated with a delimiter of comma here. Since it is an aggregate function or a multi-row function or a group function, since I'm using the department number here, I'm using the group by class also. So this is the very base functionality introduced in 11g. Let us see the example first. Then we'll see what are all the enhancements done in 12c and in 19c. Okay I have actually connected to 11g database. Let me just show you the v$version table. So you can see here I have just connected to Oracle 11.2 database. So I'm creating a table T with two columns, department number and employee name. I'm populating exactly the same information. Right, I'm populating Ravi, Raghu, Raghu. See, I've actually populated Raghu twice because I intentionally populated this information because I just want to show you one concept. Fine. So let me just commit the data. Okay, now we have queried the information. Suppose if I want to convert this information into comma separated information, we can say department number comma list aggregation of ename. You need to give any one special character. Let me just say arrow symbol like this or you can use any special character like comma or something. So then I will say within group you say order by ename. from t group by department number. Now you can see we just converted this information into a string. Here you can see the ragu is displayed twice. That is because in our table, For department number, we have inserted Raghu twice. If at all you don't want this duplicate field here, then it is not possible to include the distinct keyword directly as part of list aggregation function. This is one limitation in Oracle 11g. Okay, but how can I do this then? Okay, let us just take the same query. So my intention is basically to remove the duplicate. So let me just remove the distinct here. So now what I'm going to do instead of T, I'm just going to say select department number, comma, ename from T. So here I'm going to use the distinct keyword. So basically what I'm doing here is first I am writing an inner query to remove all the duplicate entries. After Removing the duplicate information in the outer query, I'm using the list aggregation for concatenation. So this is a workaround. But one point to keep it in mind is this is a limitation in Oracle 11g. and in fact the same thing applicable to 12c also. Fine. Now let us see one more example. So this is a very simple example where we have just concatenated the information of very simple dataset. Let us assume that we have like a huge list of name and I want to generate a concatenated list of enames. So further what I'm going to do, I'm just creating another table called t with only one column ename. Here I am populating 1000 employee names. In a loop I have just populated. Let me just commit. Let me just show you the records from the table. You can see here, the names are populated from like name1 till name1000. You can see here. So there are 1000 names are inserted into this table. Now let me try to use the list aggregation function. saying that ename, let's say comma. Now if you see, I missed the within group class, so within group, say order by ename. Now you will get an error saying that the concatenation, concatenated string is too long. See, this is another limitation in Oracle 11g. That is, whenever the length of the concatenated string is more than 4000, Oracle will not be able to concatenate the string and it will straight away throw the error saying that result of concatenation is too long. Let me just show you. Suppose, let me put like less than or equal to let's say 200. See, now you can see that Arclay is actually concatenating 200 names. Let me just make it as 250. Still it is fine. So let us take the length to see what is the maximum length of the concatenated string. So in this case it is 1891, the total length of the concatenated string. Let me make it as 500. So still it is fine. Let us make it as 550 names. That means we are trying to concatenate 550 name. Till 500 it is perfectly working fine but the moment we make it as 550 we are just getting an error saying that the result of string concatenation is too long. This is because whenever the length of the concatenated string exceeds 4000 character, Oracle will throw the exception saying that result of concatenation is too long. So these are the two limitations with respect to learng. Okay, so as I mentioned, there are two limitations here. The first limitation is we will not be able to use the distinct keyword as part of the list aggregation function in 11g. When you're trying to use it, you will get an exception saying that distinct option not allowed for this function. There is another limitation in 11g is that whenever you are trying to concatenate a string whose length is going to exceed 4000 characters, then you will get an error saying that result of string concatenation is too long. So these are the two problems with respect to 11g. Now let us start with what are the enhancements done in 12c database. Okay, there are few limitations whatever we have seen in the 11g has been addressed in 12c. You can see that in 12c, a new functionality is added for managing the situations where the length of concatenated string is too long. So in 12c, we have an option. Not to throw the exception, instead we have an option to give some meaningful information as part of the concatenated string. Now let me show you the syntax change in 12c. Then we will see the demo of how to use this. In 12c, you will see once class is additionally added as part of the syntax. You can see this, this listag overflow class is additionally introduced from 12c. To be precise, this is introduced from 12.2. Using this, we can handle the situations whenever the length of the concatenated string is more than 4. thousand characters. So here is the syntax of that list tag overflow class. We have actually two options. One is like we can specify on overflow error. When we specify on overflow error, the behavior is very similar to what we have seen. in the 11g. That means whenever it exceeds 4000 character, it will throw an error. Otherwise, we have an option to specify onOverflow truncate. When you say onOverflow truncate, whenever the length of the concatenated string exceeds 4000 character, Oracle will automatically truncate the remaining portions and it will add one truncate indicator. By default, triple dot is the truncate indicator. Whenever you are seeing triple dot in the concatenated string that means there is some information truncated after that. The default truncate indicator is triple dot but if you want to override you can even override using this option. Okay then we have two more options like with or without count. By default, whenever oracle truncates the information it will specify how many elements it has actually truncated. It will put one count at the end. That is after the triple dot it will put that count. But if you don't want that count you can specify without count. So if you specify with count it will specify the count and this is the default behavior. If you don't want the count to be displayed you can specify the without count also. So let me just quickly show you the syntactical difference between the 11g and 12c. You can see here is the 11g syntax and here is the 12g. 12c syntax. In 12c, you can see only this list ag overflow class is additionally introduced. Fine. So in fact, this information I have already covered in detail in 12.2 video. So I will directly jump into the demo. Now I have connected to Oracle 12.2 database. Let me just query from the V$ version. So you can see here I have connected to Oracle 12.2 standard edition database. So let us redo the same example whatever we have done in Oracle 11g database. So I am creating the table and I am just populating the same set of employees. So using the same query, using the list aggregation, of course we will be able to convert the department wise comma separated or a delimited separated list of names okay anyway here also we will not be able to use the distinct keyword this limitation still exists in 12c also so let me just remove the distinct keyword here so as a workaround if you want to remove the duplicate elements you can handle that in your inner query and in your outer query you can use the list aggregation as as shown here fine now let us try to redo the second example because that is where the enhancements done in 12c so let me just drop the table t creating the table t again i'm just populating thousand employees let us just query for commit that information so let me just query from the table right so we have like actually thousand employees populated in this table fine you can see 1000 enames are populated fine now what i'm going to do okay let me just remove the entire information okay now the default behavior is whenever the concatenated string exceeds 4000 character by default it will throw the exception saying that string concatenation is too long but from oracle 12.2 we have the additional class we can specify saying that on overflow truncate When you see onOverflow truncate, it won't throw the error. Let me just remove the length function. You can see here, now it is no more throwing the exception, but instead it would have truncated. You can see at the end of the string, you can see here. it has truncated so it just put triple dot whenever there is a triple dot it means that there is a truncation and finally it will say that there is a count that means 497 elements it has truncated okay now the instead of triple dot of course you can use some um Your own custom information. In fact, the default overflow indicator you can override here. You can just give some meaningful information instead of triple dot. For example, some more names exist like that. Now let us see. Now instead of the triple dot, you can see that it will just say some more name exists. Suppose if you don't want the count to be displayed, you can say without count. The default behavior is with count. Let me just show you that. Without count. Now you can see the count will not get displayed. It will just simply display only some more element exists or some more names exist. Okay, now we will look into what are all the new enhancements done in 19c. Okay, so here is the syntax of 19c. You can see two difference with respect to 12c. One is like you can see the distinct. keyword is introduced in 19c also one more difference is the within group class is no more a mandatory class from 19c if you are not mentioning the within group class by default it will order the elements based on the group class based on the ascending order. If you want to override it with descending order, you can specify the within group by class. So the rest of the functionalities are very similar to whatever you have seen in 12c. So let us see the syntax of both. You can see here in 19c, you will find the distinct. Also, the within group class is no more a mandatory class. However, in 12c, you can see the within group class is a mandatory class. So you have to specify this within group class. However, in 19c, it's not mandatory to specify this. Also, the distinct keyword you can see. Let us see the example now. Let us take the same example whatever we have seen in 11g and in 12c demo. So what we have basically did is we just converted the department wise names into a comma separated list of names. You can see here because the ragu is present twice in 11g and 12c. It will just print out twice because if you want to handle this duplicate, if you want to remove this duplicate element, then you need to handle in the inner query. However, from 19c, you can specify the distinct keyword directly like this. Also, you can see this within group by class is mandatory in 11g and 12c. However, it's not necessary to specify in 19c. Now, let us see the demo in 19c database. Now let me show you the same demo in 19c database. Let me just query from the V$ version. You can see that this is 19c Enterprise Edition database. So let me just create the table T. Sorry, it's already there. Let me first drop the table. The table is dropped. So I'm just creating the table. The table is created. So let me just populate the same set of employees. Informations are populated. Now you can see, let me just first query the list tag function as it is. You can see this ragu is printed twice. This query is very similar to whatever you have seen in 11g also in 12c. Now here we can use the distinct keyword. That is the enhancement done in 19c. Now you can see the ragu is not getting printed. twice. You can handle the distinct as well as the list aggregation in a single query itself. From 19c onwards, it's not necessary that we have to handle in the inner query and the aggregation in the outer query. Everything we can handle in the same query itself. Also this within class is also not a mandatory we can even remove this in class still it will work by default it will get sorted in the ascending order only so these are the enhancements done in 19c rest of all the functionalities with respect to the overflow aggregations same thing will get applicable in 19c also If you have learned something new, please like this video, subscribe and stay tuned for new feature video, interview question, SQL practical question, concept videos and performance tuning videos. If you want any questions to be answered, you can post it in the comment section or you can drop to the email id. And thanks a lot for watching this video. Please click the bell icon if you want to receive instant notification whenever new videos are uploaded into this channel. Thank you.

So this is an aggregate function which will help us to convert a row of values into a comma separated string. For example, suppose if you have a table which contains like department wise name, then using this listag function, we can either generate a list of comma separated names like this or we can generate something like department wise comma separated information. This is the listag function. function was introduced first in 11g And in the subsequent versions of Oracle in 12c and in 19c, there are a lot of enhancements done in this function.

So in this video, we are going to see what is the base functionality introduced in 11g. And then we are going to see what are the enhancements done in 12c and in 19c. First, let us start with what is the base functionality introduced in 11g.

Let us start with the syntax introduced in the 11g. So here is the simple syntax introduced in 11g. So what we need to use is, We just need to say list act.

and whatever the column we want to convert into a comma separated list of information, then we need to specify the optional delimiter. Either we can say comma or any special character. And this is an optional parameter. If you are not giving this information, then by default null will be considered as the delimiter. That means all the information will be concatenated without any delimiter.

Then we need to say the within group class. So the within group class will say how we want to sort the elements within the component. comma separated string.

So we just need to say either order by that element or the column name ascending or descending. Let us take an example. So suppose if I want to convert this information into a department wise comma separated names, then I need to say select department number, then you say list ag, whatever the column you want to convert into comma separated list of information.

So in this case, I'm using comma as the delimiter. So you can use any special character here. And then we need to say. say within group order by employee name. That's why you can see all the employees working in department 10 are sorted based on the ascending order and then concatenated with a delimiter of comma here.

Since it is an aggregate function or a multi-row function or a group function, since I'm using the department number here, I'm using the group by class also. So this is the very base functionality introduced in 11g. Let us see the example first.

Then we'll see what are all the enhancements done in 12c and in 19c. Okay I have actually connected to 11g database. Let me just show you the v$version table. So you can see here I have just connected to Oracle 11.2 database. So I'm creating a table T with two columns, department number and employee name.

I'm populating exactly the same information. Right, I'm populating Ravi, Raghu, Raghu. See, I've actually populated Raghu twice because I intentionally populated this information because I just want to show you one concept.

Fine. So let me just commit the data. Okay, now we have queried the information. Suppose if I want to convert this information into comma separated information, we can say department number comma list aggregation of ename. You need to give any one special character.

Let me just say arrow symbol like this or you can use any special character like comma or something. So then I will say within group you say order by ename. from t group by department number. Now you can see we just converted this information into a string. Here you can see the ragu is displayed twice.

That is because in our table, For department number, we have inserted Raghu twice. If at all you don't want this duplicate field here, then it is not possible to include the distinct keyword directly as part of list aggregation function. This is one limitation in Oracle 11g. Okay, but how can I do this then?

Okay, let us just take the same query. So my intention is basically to remove the duplicate. So let me just remove the distinct here.

So now what I'm going to do instead of T, I'm just going to say select department number, comma, ename from T. So here I'm going to use the distinct keyword. So basically what I'm doing here is first I am writing an inner query to remove all the duplicate entries.

After Removing the duplicate information in the outer query, I'm using the list aggregation for concatenation. So this is a workaround. But one point to keep it in mind is this is a limitation in Oracle 11g. and in fact the same thing applicable to 12c also.

Fine. Now let us see one more example. So this is a very simple example where we have just concatenated the information of very simple dataset.

Let us assume that we have like a huge list of name and I want to generate a concatenated list of enames. So further what I'm going to do, I'm just creating another table called t with only one column ename. Here I am populating 1000 employee names.

In a loop I have just populated. Let me just commit. Let me just show you the records from the table.

You can see here, the names are populated from like name1 till name1000. You can see here. So there are 1000 names are inserted into this table. Now let me try to use the list aggregation function. saying that ename, let's say comma.

Now if you see, I missed the within group class, so within group, say order by ename. Now you will get an error saying that the concatenation, concatenated string is too long. See, this is another limitation in Oracle 11g.

That is, whenever the length of the concatenated string is more than 4000, Oracle will not be able to concatenate the string and it will straight away throw the error saying that result of concatenation is too long. Let me just show you. Suppose, let me put like less than or equal to let's say 200. See, now you can see that Arclay is actually concatenating 200 names.

Let me just make it as 250. Still it is fine. So let us take the length to see what is the maximum length of the concatenated string. So in this case it is 1891, the total length of the concatenated string. Let me make it as 500. So still it is fine.

Let us make it as 550 names. That means we are trying to concatenate 550 name. Till 500 it is perfectly working fine but the moment we make it as 550 we are just getting an error saying that the result of string concatenation is too long.

This is because whenever the length of the concatenated string exceeds 4000 character, Oracle will throw the exception saying that result of concatenation is too long. So these are the two limitations with respect to learng. Okay, so as I mentioned, there are two limitations here. The first limitation is we will not be able to use the distinct keyword as part of the list aggregation function in 11g.

When you're trying to use it, you will get an exception saying that distinct option not allowed for this function. There is another limitation in 11g is that whenever you are trying to concatenate a string whose length is going to exceed 4000 characters, then you will get an error saying that result of string concatenation is too long. So these are the two problems with respect to 11g.

Now let us start with what are the enhancements done in 12c database. Okay, there are few limitations whatever we have seen in the 11g has been addressed in 12c. You can see that in 12c, a new functionality is added for managing the situations where the length of concatenated string is too long.

So in 12c, we have an option. Not to throw the exception, instead we have an option to give some meaningful information as part of the concatenated string. Now let me show you the syntax change in 12c. Then we will see the demo of how to use this. In 12c, you will see once class is additionally added as part of the syntax.

You can see this, this listag overflow class is additionally introduced from 12c. To be precise, this is introduced from 12.2. Using this, we can handle the situations whenever the length of the concatenated string is more than 4. thousand characters. So here is the syntax of that list tag overflow class. We have actually two options.

One is like we can specify on overflow error. When we specify on overflow error, the behavior is very similar to what we have seen. in the 11g. That means whenever it exceeds 4000 character, it will throw an error. Otherwise, we have an option to specify onOverflow truncate.

When you say onOverflow truncate, whenever the length of the concatenated string exceeds 4000 character, Oracle will automatically truncate the remaining portions and it will add one truncate indicator. By default, triple dot is the truncate indicator. Whenever you are seeing triple dot in the concatenated string that means there is some information truncated after that. The default truncate indicator is triple dot but if you want to override you can even override using this option. Okay then we have two more options like with or without count.

By default, whenever oracle truncates the information it will specify how many elements it has actually truncated. It will put one count at the end. That is after the triple dot it will put that count.

But if you don't want that count you can specify without count. So if you specify with count it will specify the count and this is the default behavior. If you don't want the count to be displayed you can specify the without count also. So let me just quickly show you the syntactical difference between the 11g and 12c. You can see here is the 11g syntax and here is the 12g.

12c syntax. In 12c, you can see only this list ag overflow class is additionally introduced. Fine. So in fact, this information I have already covered in detail in 12.2 video. So I will directly jump into the demo.

Now I have connected to Oracle 12.2 database. Let me just query from the V$ version. So you can see here I have connected to Oracle 12.2 standard edition database.

So let us redo the same example whatever we have done in Oracle 11g database. So I am creating the table and I am just populating the same set of employees. So using the same query, using the list aggregation, of course we will be able to convert the department wise comma separated or a delimited separated list of names okay anyway here also we will not be able to use the distinct keyword this limitation still exists in 12c also so let me just remove the distinct keyword here so as a workaround if you want to remove the duplicate elements you can handle that in your inner query and in your outer query you can use the list aggregation as as shown here fine now let us try to redo the second example because that is where the enhancements done in 12c so let me just drop the table t creating the table t again i'm just populating thousand employees let us just query for commit that information so let me just query from the table right so we have like actually thousand employees populated in this table fine you can see 1000 enames are populated fine now what i'm going to do okay let me just remove the entire information okay now the default behavior is whenever the concatenated string exceeds 4000 character by default it will throw the exception saying that string concatenation is too long but from oracle 12.2 we have the additional class we can specify saying that on overflow truncate When you see onOverflow truncate, it won't throw the error.

Let me just remove the length function. You can see here, now it is no more throwing the exception, but instead it would have truncated. You can see at the end of the string, you can see here. it has truncated so it just put triple dot whenever there is a triple dot it means that there is a truncation and finally it will say that there is a count that means 497 elements it has truncated okay now the instead of triple dot of course you can use some um Your own custom information. In fact, the default overflow indicator you can override here.

You can just give some meaningful information instead of triple dot. For example, some more names exist like that. Now let us see. Now instead of the triple dot, you can see that it will just say some more name exists.

Suppose if you don't want the count to be displayed, you can say without count. The default behavior is with count. Let me just show you that. Without count. Now you can see the count will not get displayed.

It will just simply display only some more element exists or some more names exist. Okay, now we will look into what are all the new enhancements done in 19c. Okay, so here is the syntax of 19c.

You can see two difference with respect to 12c. One is like you can see the distinct. keyword is introduced in 19c also one more difference is the within group class is no more a mandatory class from 19c if you are not mentioning the within group class by default it will order the elements based on the group class based on the ascending order.

If you want to override it with descending order, you can specify the within group by class. So the rest of the functionalities are very similar to whatever you have seen in 12c. So let us see the syntax of both. You can see here in 19c, you will find the distinct.

Also, the within group class is no more a mandatory class. However, in 12c, you can see the within group class is a mandatory class. So you have to specify this within group class. However, in 19c, it's not mandatory to specify this.

Also, the distinct keyword you can see. Let us see the example now. Let us take the same example whatever we have seen in 11g and in 12c demo.

So what we have basically did is we just converted the department wise names into a comma separated list of names. You can see here because the ragu is present twice in 11g and 12c. It will just print out twice because if you want to handle this duplicate, if you want to remove this duplicate element, then you need to handle in the inner query. However, from 19c, you can specify the distinct keyword directly like this.

Also, you can see this within group by class is mandatory in 11g and 12c. However, it's not necessary to specify in 19c. Now, let us see the demo in 19c database. Now let me show you the same demo in 19c database.

Let me just query from the V$ version. You can see that this is 19c Enterprise Edition database. So let me just create the table T. Sorry, it's already there. Let me first drop the table.

The table is dropped. So I'm just creating the table. The table is created.

So let me just populate the same set of employees. Informations are populated. Now you can see, let me just first query the list tag function as it is.

You can see this ragu is printed twice. This query is very similar to whatever you have seen in 11g also in 12c. Now here we can use the distinct keyword. That is the enhancement done in 19c.

Now you can see the ragu is not getting printed. twice. You can handle the distinct as well as the list aggregation in a single query itself.

From 19c onwards, it's not necessary that we have to handle in the inner query and the aggregation in the outer query. Everything we can handle in the same query itself. Also this within class is also not a mandatory we can even remove this in class still it will work by default it will get sorted in the ascending order only so these are the enhancements done in 19c rest of all the functionalities with respect to the overflow aggregations same thing will get applicable in 19c also If you have learned something new, please like this video, subscribe and stay tuned for new feature video, interview question, SQL practical question, concept videos and performance tuning videos.

If you want any questions to be answered, you can post it in the comment section or you can drop to the email id. And thanks a lot for watching this video. Please click the bell icon if you want to receive instant notification whenever new videos are uploaded into this channel. Thank you.

Transcript for:Overview of Oracle LISTAGG Function

Transcript for:
Overview of Oracle LISTAGG Function