Using LISTAGG or NS_CONCAT in a SuiteQL query

I am attempting to concatenate all the rows in a column into a single comma separated string, but I receive an “Invalid or unsupported search” error anytime I try to use the LISTAGG or NS_CONCAT functions in my query string. It works properly in a Saved Search using the following formula:

LISTAGG({number}, ‘,’)

But the following SuiteQL query throws an error:

SELECT LISTAGG(number, ‘,’) FROM transaction WHERE recordtype=’salesorder’;

Any help concatenating rows in a SuiteQL query would be greatly appreciated.

Rookie Asked on February 15, 2021 in SuiteAnalytics.

The following is from SuiteAnswers:


Resolve Error: “Syntax Error in the SQL Statement” When Using LISTAGG Function in ODBC

Published 06/12/2020 05:06 PM   |    Updated 03/31/2021 03:26 AM   |    Answer Id: 93558

Applies To 

Product:  NetSuite 2020.2


The user wants to use LISTAGG function on their ODBC query, however, they get the Error: “Syntax Error in the SQL statement”. 


The LISTAGG function is not supported by SuiteAnalytics Connect. It’s not compliant with SQL-92.

on May 31, 2021.
Add Comment
2 Answer(s)

Not sure if this helps but in a saved search ns_concat only works with a Minimum or Maximum Summary Type.  The field subjected to the ns_concat function cannot be grouped by itself

Rookie Answered on February 15, 2021.
Add Comment

In Netsuite 2022.1, when I run the following query using the N/query.runSuiteQL() function, I am able to get results successfully.  I’m not sure if this also works with SuiteAnalytics Connect.


LISTAGG (tranid, ',') WITHIN GROUP(ORDER BY tranid) AS document_numbers,

trandate AS date




rownum < 10

GROUP BY trandate

It would appear that there is a character limit on each result, although I’m not sure what it is. I would recommend starting with a small number of results to make sure your formula works and then increasing the results from there.

Rookie Answered on July 12, 2022.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor   Become a Sponsor