How to loop over query data and insert in structure?

Question

I have query data that should be inserted in the structure. While looping over data each value should be assigned to matching column. Here is example of query data:

    REC_ID  NAME    STATUS      TYPE    YEAR
1   01      PARIS   Approved    1       2019
2   06      AUSTIN  Inactive    3       2017
3   48      LONDON  Approved    2       2018
4   43      ROME    Inactive    5       2019
5   61      DUBLIN  Inactive    4       2019

This data should be organized to look like this:

1
  REC_ID  01
  NAME    PARIS
  STATUS  Approved
  TYPE    1
  YEAR    2019
2
  REC_ID  06
  NAME    AUSTIN
  STATUS  Inactive
  TYPE    3
  YEAR    2017
3
  REC_ID  48
  NAME    LONDON
  STATUS  Approved
  TYPE    2
  YEAR    2018
4
  REC_ID  43
  NAME    ROME
  STATUS  Inactive
  TYPE    5
  YEAR    2019
5
  REC_ID  61
  NAME    DUBLIN
  STATUS  Inactive
  TYPE    4
  YEAR    2019

I tried to get desired data format with this function:

function formatData(qryData) {
    local.fnResult = structNew();
    local.fnData = structNew();

    if(qryData.recordcount){
        for(row in qryData) {
            for(column in qryData.columnList) {
                local.strRec = structNew();
                structInsert(strRec, column, row[column]);
                local.fnData[qryData.currentrow] = strRec;
            }
        }
    }

    writeDump(fnData);
}

Here is how my result looks:

1
  REC_ID  01
2
  REC_ID  06
3
  REC_ID  48
4
  REC_ID  43
5
  REC_ID  61

I use ColdFusion 11 cfscript syntax. If anyone can help me solve this problem please let me know. Thank you.

Answer 1

If I understand this correctly, it looks like you could just

array function formatData(qryData) {
   return DeserializeJSON(SerializeJSON(arguments.qryData, "struct"));
}

Runnable example on trycf.com

Result:

Alternative

array function formatData(qryData) {
   var result = [];

   for (var row in qryData) {
       result.append(row);
   }       

   return result;
}

Answer 2

@espresso_coffee, Here I've go through your problem. I hope you need to create a structure with key as row count and each key value should be in a structure format. Here I've provide some sample code for with my query. I hope it's useful to you.

<cfquery name="readStudent" datasource="student">
    select * from  user
</cfquery>

In my user table having 6 records with firstname, lastName & id. Here I've loop over the query and convert it in to structure key values. I've used script syntax because your code having script style. :)

<cfscript>
    myStr = {};
    for (row in readStudent) { 
        structInsert(myStr, #readStudent.currentrow#, row);
    }
    writeDump(myStr);
</cfscript>

Here I've create my first structure name as myStr, and loop over query data and insert a structure value for key with current row that is readStudent.currentRow and value is in row . The row having firstname,lastname & id as structure key & it's value.

FYR : I've attached my sample query and converted to structure value. Correct me if I'm wrong understood your problem

I hope it's helpful to you. Thanks.

Answer 3

There is an issue with your column list loop, you are creating a new structure for each column and that get reset in the next loop.

This loop should be changed

for(column in qryData.columnList) {
    local.strRec = structNew();
    structInsert(strRec, column, row[column]);
    local.fnData[qryData.currentrow] = strRec;
}

to look like

local.strRec = structNew();
for(column in qryData.columnList) {
    structInsert(strRec, column, row[column]);
}
local.fnData[qryData.currentrow] = strRec;