Hi everyone, This isn't strictly a CFML question... It might be "JUST" an SQL problem - but since I can't solve it for myself in SQL, I'm open to solutions that may need some massaging in CFML, to get it working. Basically I need to create a join between two tables, where one side of the join is a column name, not a value in a column. I have created a dbFiddle to better explain. (I don't think I need a "coded" solution. A psuedo-code / steps required - will most likely be all I need) As always - thanks in advance for any ideas you might have.

@gavinbaumanis just an FYI there is a SQL channel #sql for these type of questions.

@gavinbaumanis I gave it a shot transposing the column and taking some help from information_schema (i think this only works in sql8+)

SELECT 
v.survey_id,
concat(
  (CASE WHEN internalName='PvFf9Bzd_response' THEN questionText ELSE '' END),
  (CASE WHEN internalName='yW3AEXOT_response' THEN questionText ELSE '' END),
  (CASE WHEN internalName='IES35zCj_response' THEN questionText ELSE '' END),
  (CASE WHEN internalName='IES35zCj_response_reason' THEN questionText ELSE '' END)
) AS question,
v.IES35zCj_response_reason

from vxml_xxxxxx v inner join ws_question ws on v.id = ws.id
where ws.internalName in (
select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = "vxml_xxxxxx" AND COLUMN_NAME like "%response%") 
;

produces output like so:

I was going to suggest using

PIVOT

, but it looks like mysql doesn't have that...

If you are open for it not being pure SQL ... and since I don't know your desired result: I would query for your recordset that you want to output somehow... then in your recordset i would find the columns whose names are _response then i would query for the labels for those columns and store those in a Struct _Columns["PvF...response"]="ON a scale..." then you could loop over the rows and then the columns that you want output.... i don't know how you want the output....

@Michael Schmidt The existing report has survey_id, date, recipientName, recipientTHIS, recipientTHAT, answer_XX1 answer_XX2, ... I have been asked to include the text of the question before the answer in the existing report. do I would have; survey_id, ..., recipientTHAT, question_XX1_text, answer_XX1, question_XX2_text, answer_XX2, ...