LISTAGG code like

[TheLazyPig]

Is there a LISTAGG code in foxpro9 or any similar process?

Thank you for the response.

 

[mJindrova]

Merging Rows into one | Foxite

 

[Chris Miller]

No

Edit: I don't disagree with mJindrova, you just don't have such an aggregation in the VFP language, you need to program a function or use some other mechanism like SCAN..ENDSCAN of a result to build up a CSV string or whatever aggregation you think of.

SQL Server only had FOR XML PATH for a while, now it also has a dedicated STRING_AGG function (since 2017). If your data is in SQL Server, you better do it there with that function than as VFP aftermath.

 

[Chris Miller]

To steal and improve the idea of Brian Walsh, here's a more generally working StringAgg function:

Function StringAgg
   Lparameters SourceTable, AggregationField, groupby, groupid
   Local lcAggString
 
   Try
      Select curSringAgg
   Catch
      Create Cursor curSringAgg (Id Int, aggregation M)
   Endtry

   Locate For Id = groupid
   If Not Found()

      Select &AggregationField as cListItem From (SourceTable) Where &groupby = groupid Order by &AggregationField Into Cursor curTemp

      Select curTemp
      lcAggString= ""
      Scan
         lcAggString = lcAggString + ","+Transform(cListItem)
      Endscan
      Use in curTemp

      Insert Into curSringAgg Values (groupid, Substr(lcAggString,2))
   Endif

   Return curSringAgg.aggregation
Endfunc

A usage example is

Open Database (_samples+"Northwind\Northwind.dbc")

Select Orders.OrderID, Cast(StringAgg("OrderDetails","Productid","OrderID", Orders.OrderID) as V(254)) as products from orders ;
Left Join OrderDetails on OrderDetails.orderid = orders.orderid;
Group By 1;
into cursor result

It also shows the limitd practicality, as it aggregates the product ids of orderitems into a list per orderid. What you'd likely want to aggregate is product names.

It's simply to also join products on the productid, to have product names available in the overall select, but within the StringAgg function it becomes hardly workable to have a general SQL select that covers the necessary tables to get to the detail field you actually want to aggregate. If you don't want to go the route to speciy this core SQL Select fully as a parameter of StringAgg, you're not getting a good generally working function.

What I did improve is that the way it is it's covering simple two table cases in a general way, Brian Walsh did hardwire the involved tables in his aggregation function. Also I created a cursor for storing all the aggregtaion lists, in the case the same groupid comes in as a parameter twice. After testing I think that overhead could be removed, as you only get one call per orderid, but I'm not sure how this would work in a more complex case.

What's not ideal is that StringAgg can't clean up after itselff and close the curSringAgg cursor. Because it can't know when it's called for the last time. So before using StringAgg in further SQL you'd need to close curSringAgg to not get results of previous curSringAgg. That problem might siply disappear if you rely on curSringAgg only being called once per distinct groupid, then there's no need for the caching mechanism that curSringAgg is.

What you don't have, that the sql engine has available is a workarea where all fields of all joined tables are available. And you don't havbe a simple way to specify wanted fields, that's what's making it harder to ex the sql functions like ListAgg. While all relevant tables are open during the SQL execution of the main query, you don't have the insight which workareas the sql engine actually involves in the query exeution and where you're getting which tables fields. Even if you would, you wouldn't want to touch that or you get broken results and side effects in what the main VFP sql engine does with its workareas.

It might be worth digging a bit deeper to get an even more generally working StringAgg function. ideally not needing too much parameters you'd have to think about each time you write a StringAgg call. Because otherwise you're getting off with less work doing a simple SCAN..ENDSCAN yourself in each individual case.

 

[Chris Miller]

A shorter version of StringAgg would be

Function StringAgg
   Lparameters SourceTable, AggregationField, groupby, groupid
   Local lcAggString
 
   Select &AggregationField as cListItem From (SourceTable) Where &groupby = groupid Order by &AggregationField Into Cursor curTemp

   Select curTemp
   lcAggString= ""
   Scan
      lcAggString = lcAggString + ","+Transform(cListItem)
   Endscan
   Use in curTemp

   Return Substr(lcAggString,2)
Endfunc

And since the StringAgg function determines the aggregated list of OrderDetails productid you could even simplyfy the main select to not join OrderDetails itself and even not grooup by, as that's all done withing StringAgg:

Open Database (_samples+"Northwind\Northwind.dbc")

Select Orders.OrderID, Cast(StringAgg("OrderDetails","Productid","OrderID", Orders.OrderID) as V(254)) as products from orders ;
into cursor result

So the main query that calls StringAgg is even simpler.

 

[TheLazyPig]

Hi! I guess I have to create a separate program to use the function for future requests like this.

Thank you for the responses!

 

[Chris Miller]

That will be practical, like ListAGG,prg being the core of the function code, i.e. starting with LPARAMETERS.

It could be improved for more general or more specific usage, so you might also just create an agg prg for individual cases with this pattern in mind; They function mainly needs to know the group key and find the items of the group for the concatenation into a comma separated values string. Or watever other aggregation that's not doable by the native aggregaition functions available for SQL - MIN( ), MAX( ), SUM( ), AVG( ), COUNT( ), NPV( ), STD( ) or VAR( )) or for CALCULATE.