I want an SQL SELECT statement to return mupltiple values

[peewee]

In a Details Page in a report I want to list all the Key_Concept_Name instances related  to a certain Learning_Outcome_ID.
I created the following SQL statement in a calculated field: SELECT Key_Concept_Name FROM Key_Concepts WHERE Learning_Outcome_ID = [@field:LO_ID]
Only the first Key_Concept_Name instance is returned. Usually there are more.

How is it possible to return all the Key_Concept_Name instances?

[Vitalikssssss]

@peewee hi,

I guess this should help:

 

[peewee]

Thanks, Vitalikssssss.

This works like a charm.

By the way, is there any document listing the SQL syntax allowed with CASPIO?

[CoopperBackpack]

Hello @peewee,

You may check the list of supported functions in this article https://howto.caspio.com/function-reference/

Let me add more details about the FOR XML PATH clause for the future reference. 

 

For example, there is the table:

1) To display a comma-separated list of names: 

STUFF((SELECT ', ' + (EmployeeName) FROM EmployeeTable WHERE id > 1 ORDER BY EmployeeName FOR XML PATH ('')), 1, 1, '') 

The output:  Ann, John, Tom


2) To display a list of names with line breaks: 

STUFF((SELECT CHAR(10) + (EmployeeName) FROM EmployeeTable WHERE id > 1 ORDER BY EmployeeName FOR XML PATH ('')), 1, 1, '')

The output:

Ann
John
Tom

Note: WHERE clause, ORDER BY keyword are optional. Replace the field name and table name.
  

[Meekeee]

Hi - Just an addition to this, if you would like to display multiple values (comma and quotation marks separated), you can use this formula:

STUFF((SELECT ', ' +'"'+ (FIELDNAME) +'"' FROM TABLE_NAME FOR XML PATH ('')), 1, 1, '')

Result:
"test1.com", "test2.com", "test3.com", "test4.com"