Menu

Turning output rows into a single comma delimited list with Sub Queries

 

This article shows how to create a comma delimited list of values group by some other field using Sub Queries.  In this example we have a list of Customers and we want to know all the Item Groups that they have been invoiced on.  However we want the Item Groups to appear as a comma delimited list on one row instead of one per row.

The basic steps are to create the main query that lists the Customers and then a Sub Query to get all the Item Group for that Customer using Summary Report Mode.  The Sub Query has an Expression to concatenate the results together while another Expression ensures that just the last row is returned to the parent.

 

Preview of Data showing the before and after conversion of the Item Groups into a comma delimited list

CommaDelimitedList-Results-1.png

First create a Query to get the basical Customer list, Items Groups and Balance to confirm what the data would look like before putting the Item Groups into one comma delimited list.  Later when before creating the Sub Query the parent Query Item Groups output can be removed.

CommaDelimitedList-BaseQuery-2.png

Now delete the output Items Group Name as this will be replaced with the Output Sub Create.  Use the right click menu on the outputs pane to select Add Sub Query

Set the Query Mode to Summary Report and Link the Sub Query filters to the parent filters {@staging-sharperlight-com.stackstaging.comname} or parent outputs {%name} by using the drop down lists. This will ensure we get the same Customer Item Groups as on the Parent row
In the next step we will add an Expression to concatenate the Item Groups along with a second Expression to just return the last row

CommaDelimitedList-SubQuery-3.png

Because the Expression will be concatenating values to itself you will first need to give the Expression a Name, close the Window and then reopen it so that the Expression appears in the Previous Row list
When the Customer Code is different then start the list otherwise add the Item Group to itself with a comma

  • IIF( {%Previous:CusCod}<>{%CusCod}
    ,{%ItemGroupName}
    ,ConcatString({%Previous:GroupList},{%ItemGroupName},”, ” )
    )​

 

CommaDelimitedList-SubQueryExpression-4.png
Test the Expression by clicking on Preview and entering some test filters for one of the Customers

You can see the Expression is with each row adding the current Item Group to itself.  The last row will be the complete list of Item Groups for the Customer.  In the next step we will create a Expression to just return the last row which contains all the Item Groups

CommaDelimitedList-SubQueryExpression-5.png

 

This Expression function RowSkipAllButLast() will change the Query Results so that just the last row is returned.  It is the last row that contains the complete list of Item Groups

CommaDelimitedList-SubQueryExpression-6.png

Because this is a Sub Query and by default they should only return one row to the parent a prompt will come up on closing the window to ask if you want to force one row with a top 1 command.  Answer NO to this as we need all the rows and the Expression RowSkipAllButLast is making sure that only one row is finally returned

​CommaDelimitedList-SubQueryExpression-7.png

Give the Sub Query a Description and Preview the Final Report

CommaDelimitedList-FinalQuery-8.png

 

 

 

Published: 16/03/2016 7:10 PM

Related Posts