Create a single CSV from multiple AND unmatched queries? (my use case is about 20 queries, with a combined distinct column count of over 30) Before I go and try and do it myself - I thought I would ask if there was a "magic" way to do the following already? At the moment I don't think I need any help in writing my own code - I am just asking if there is something that already exists to do it for me? ( • I have reviewed the CFSpreadsheet library from Julian Halliwell / @cfsimplicity - and from reading the docs, it doesn't look like it does what I need in a blackbox / auto-magic way. • I have also seen Ben Nadel's @ben queryToCSV() blog post - but I don't have a single query. ) Currently we have THIS in place and it is working; Looping through a list of customers Looping through a list of questionnaires Each questionnaire has a unique number of questions Each question has a specific return type. Using a UDF that takes into account a bunch of customer's reporting requirements / customisations THIS UDF returns the string of the required SQL to run Run the SQL created Create a CSV header row of columnNames Add the data from the query to the CSV Save a CSV for THIS questionnaire What we need to create, that we don't currently have working is; All the above - BUT ONE combined csv for all questionnaires. As way of illustration only - I have three different queries, for three different questionnaires. if a question exists in more than one questionnaire, it is ALWAYS the same text and ALWAYS the same data type. "question_1" exists in all three queries and is the same data type "question_2" is ONLY in query 1 (along with question_1) "question_3" is ONLY in query 2 (along with question_1) "question_4" is only from query 3 (along with question_1) What I am hoping to do is create one CSV with 4 columns in total. Where a column does NOT exist in a query, then that "cell" should be NULL. (Below; • "YES" - is just a placeholder for the example • There is only one row returned from each query in this example ) rowFromQuery question_1 question_2 question_3 question_4 1 "YES" "YES" NULL NULL 2 "YES" NULL "YES" NULL 3 "YES" NULL NULL "YES" We have re-arranged the looping, So that we can create a single header row of distinct columnNames (questions) successfully. We can add as many rows as their are results from all queries, into the CSV We have not yet, correctly managed to (is "*qualify"* the right term) the data So instead of what we want above, we end up with the following in a CSV question_1 question_2 question_3 question_4 "YES" "YES" "YES" "YES" "YES" "YES" Which is obviously wrong. Again - I am sure we can we can work on the code we have - and get it working correctly... But if something already exists - we would save some time, developing and testing. As always - Thanks!

the udf that returns the sql to run for each customer/iteration (yuck) needs to be handling the non-existent columns, so that each of those sql statements returns the four columns:

SELECT questionnaireId, 
	(CASE WHEN questionnaireId  = 1 THEN question_1 ELSE NULL END) AS question_1,
	(CASE WHEN questionnaireId  = 2 THEN question_2 ELSE NULL END) AS question_2,
	(CASE WHEN questionnaireId  = 3 THEN question_3 ELSE NULL END) AS question_3,
	(CASE WHEN questionnaireId  = 4 THEN question_4 ELSE NULL END) AS question_4 
FROM tablename 
WHERE customerId = 123 AND other_criteria

In your scenario, I'd probably just do a querynew() matching your csv header stuff, loop over the various queries above, on each line build a struct with the columns relevant to that query to pass to queryAddRow(), then at the end call cfml-spreadsheet querytocsv()

QoQ with UNION? https://trycf.com/gist/09a8a6504f426f7128b6b49438458909/lucee5

@websolete "the udf that returns the sql to run for each customer/iteration (yuck)" The building of the SQL is extremely complex and complicated, based on a spectacularly large number of rules. The UDF - is actually 3 separate functions, complete with recursive calls - so, as a first response - "very yuck", might be more appropriate. Truth be told - I am absolutely certain that there is a better, clearer (simpler) way to dynamically produce the needed SQL - but it works - and we have more pressing priorities. @cfsimplicity I had never even considered QoQ, for this task. And while lots of people absolutely hate it - I am very much a "horses for courses", developer. I really like your solution. I wouldn't have to change very much from what I do now. What do you all think of; Still do all the looping, dynamic SQL creation that we do now. "stuffing the SQL and the columnsList into a struct" And putting that into an array. That would give me this part of Julian's example code.

q1 = QueryNew( "Q1,Q2", "VarChar,VarChar", [ [ "yes", "yes" ] ] );
q2 = QueryNew( "Q1,Q3", "VarChar,VarChar", [ [ "yes", "yes" ] ] );
q3 = QueryNew( "Q1,Q4", "VarChar,VarChar", [ [ "yes", "yes" ] ] );

And with more some looping / matching on the all-combined columnList, I could build the UNION portions?

here is an example of what i was describing, should be acf or lucee compatible, ymmv. https://trycf.com/gist/f1401e970fd44e31b2e7540ac791e98d/acf2021?theme=monokai

Thanks @Matt Jones! Appreciate the time you spent on this for me.