How to realize column switching by grouping with listagg function

Editor to share with you how to use listagg function grouping to achieve column switching, I hope you will gain something after reading this article, let's discuss it together!

Listagg is a new function in Oracle 11.2, and listagg can sort the data in each group specified in the order by clause and then concatenate the values of those columns. The following are simple examples of applications:

SELECT deptno, listagg (ename,',') WITHIN GROUP (ORDER BY ename) AS employees FROM scott.emp GROUP BY deptno

You can experience it in the livesql.oracle.com cloud platform:

Take a look at the following query, which contains duplicate values:

Select d.dname, listagg (e.job,',') within group (order by e.job) jobs from scott.dept d, scott.emp e where d.deptno = e.deptno group by d.dname

Oracle 19C has made a small improvement, which can easily remove the weight of the conversion result and support the distinct keyword:

Select d.dname, listagg (distinct e.job,',') within group (order by e.job) jobs from scott.dept d, scott.emp e where d.deptno = e.deptno group by d.dname

After reading this article, I believe you have a certain understanding of "how to use listagg function grouping to achieve column switching". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!