Jump to content
  • 0

I want an SQL SELECT statement to return mupltiple values



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?

Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0

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:


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

Link to comment
Share on other sites

  • 0

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, '')

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

Link to comment
Share on other sites

  • 0

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, '')

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'


'Hi, This Is A Test'

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...