Roll up various records into 1 line on CSV

Discussion created by malachydevlin on Oct 2, 2016
Latest reply on Oct 2, 2016 by beverly


I have a orders table and related items within.

I have to create an import CSV file for Sage 200 and it actually takes 1 line for a sales invoice but it rolls up the different items into extra columns on the one line.

So i need to get something like this

orderno, account, full amount, code1,code1amount, code2,code2amount, code3, code3amount etc.

so the related lines have to be added with an incrementing number to new columns, its getting vary hard to read but I am not sure of the best way to get the FM data into 1 line.

I am thinking an SQL statement with a dynamic column names but its real tricky to read..