Kicking the habit of WM_CONCAT for a delimited list of rows, with LISTAGG

I gave up smoking recently.
Among other bad habits that I need to kick is using the (not so) trusty WM_CONCAT.

Say I want to get a record set consisting a comma-delimited list of columns in the EMPLOYEES table. In the past, this may have been somewhat challenging to do in a single SQL query, unless you knew about the undocumented WM_CONCAT…

select wm_concat(column_name)
from user_tab_cols
where hidden_column = 'NO'
and table_name = 'EMPLOYEES';

From around 10g, right up to 11g R2 Enterprise Edition, this function would return your result set in a single, comma-delimited list.
However, if you attempt to execute the same query in 12g, or even 11g Express Edition, you’ll get a nasty surprise …

Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00904: "WM_CONCAT": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

Fortunately, a more modern (and supported) alternative has been around since 11g…

select listagg( column_name, ',') within group( order by column_id)
from user_tab_cols
where hidden_column = 'NO'
and table_name = 'EMPLOYEES'
/

LISTAGG(COLUMN_NAME,',')WITHINGROUP(ORDERBYCOLUMN_ID)
----------------------------------------------------------------------------------------------------------------------------------
EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID

Unlike WS_CONCAT, LISTAGG allows you to specify the order in which the delimited values should be concatenated. It also allows you to specify the delimiter to use.
So you could use a “|” symbol, for example, or, if you have definite ideas about how a list of columns should be written you may consider something like :

select listagg( column_name, chr(10)||',') within group( order by column_id)
from user_tab_cols
where hidden_column = 'NO'
and table_name = 'EMPLOYEES'
/

LISTAGG(COLUMN_NAME,CHR(10)||',')WITHINGROUP(ORDERBYCOLUMN_ID)
--------------------------------------------------------------------------------
EMPLOYEE_ID                                                                     
,FIRST_NAME                                                                     
,LAST_NAME                                                                      
,EMAIL                                                                         
,PHONE_NUMBER                                                                   
,HIRE_DATE                                                                      
,JOB_ID                                                                         
,SALARY                                                                         
,COMMISSION_PCT                                                                 
,MANAGER_ID                                                                     
,DEPARTMENT_ID                      

Now, if only I could remember not to squeeze the toothpaste tube in the middle…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s