sheets query requires splitting last returned column by comma

Question

Table with data in columns A-N. Data in column 'M' contains CSV data.

Using this query can return data, need what's in 5th returned column (E) to be split into additional columns through one command. Cannot do this separately manually...

=QUERY(scores,"select A,H,K,N,M WHERE N is not null and M is not null order by K ASC, N DESC, H ASC, A DESC")

Output has to be obtained using QUERY function, as its pulling from a named-range, from a data set I cannot modify. The other columns may contain commas and are not required to be split up.

Currently the column required to be split comes from M in the original named range. When this query runs, the data needed to be split is in the 5th column (E).

Not clear how to augment what you've suggested to fit this. I was able to use your example of offset to hit the named range, but that omits the necessary limits imposed by the SQL select portions of QUERY.

Answer 1

It's not entirely clear why you insist on using QUERY().

Perhaps

=ArrayFormula({OFFSET(named-range,0,0,,2),SPLIT(OFFSET(named-range,0,2,,1),",")})

will do the job?

Finally, you can query the result of this expression -

=QUERY(ArrayFormula({OFFSET(named-range,0,0,,2),SPLIT(OFFSET(named-range,0,2,,1),",")}))
or something similar
=QUERY(ArrayFormula({OFFSET(named-range,0,0,,2),SPLIT(OFFSET(named-range,0,2,,1),",")}),
       "select * where Col4 = 333",1)

Update The literal translation of the formula given in the question looks like this:

=QUERY(ARRAYFORMULA({OFFSET(scores,0,0,,1),
                     OFFSET(scores,0,7,,1),
                     OFFSET(scores,0,10,,1),
                     OFFSET(scores,0,13,,1),
                     SPLIT(OFFSET(scores,0,12,,1),",")}),
    "select * WHERE Col4 is not null and (Col5 is not null or Col6 is not null) 
      order by Col3 ASC, Col4 DESC, Col2 ASC, Col1 DESC")