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.
  

[Kurumi]

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"

[DrSimi]

Hi - Just another addition to this using the great syntax suggested by @Kurumi, if you ever need to do something with each word in a text field or string, you can use the following:

CASE WHEN LEN('[@field:YourTextField]')>0
THEN STUFF((SELECT ' '+ UPPER(LEFT(value,1))+LOWER(SUBSTRING(value,2,LEN(value))) FROM STRING_SPLIT('[@field:YourTextField]',' ') FOR XML PATH ('')), 1, 1, '')
ELSE NULL
END

In the above, the STRING_SPLIT function is used to separate each word in a string (using space (' ') as the separator) in separate rows instead of Selecting multiple rows from a Table. Once we have each word in a row, Upper/Lower/Substring functions are used to capitalize only the first letter. Then we "merge" every row back into one with the STUFF/FOR XML PATH functions.

The CASE/WHEN was added because for some reason, if the field is blank the formula would output the whitespace hex code in a Calculated Value/Field.

Input text:

'hi, this is a test'

Result:

'Hi, This Is A Test'

See this article STRING_SPLIT (Transact-SQL) - SQL Server for reference and an example. Hope this helps someone!