Jump to content
  • 0

I want an SQL SELECT statement to return mupltiple values


peewee
 Share

Question

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

4 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:


Ru7F2o2.png


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.
  

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

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

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.

Guest
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.

Loading...
 Share

×
×
  • Create New...