CODEWITHSUNDEEP
kentico
Michelle
Michelle

Reputation: 249

Kentico - Form Control Drop-down List & SQL Query

I couldn't make the title clearer, but here's what I need help with.

I have a custom page type [1] for Leaders which includes 2 fields: Name, and Title. This holds the list of all leaders at the company.

I have another custom page type [2] for Speaking Events, which includes a field called Speaker to display the speaker's name and title. This field was set up as a drop-down list with data source from a SQL Query to query the Leaders data in [1].

Select LeadersID, Name, Title from co_leaders order by Name

I got it work fine - the drop-down displays a list of Name. However, what I wanted to display in the drop-down option is: Name, Title (not just Name) like below so that I only pick one and have both Name and Title. Is it possible to do this?

John Doe, CEO
Jane Doe, CFO

Hope it's clear and thanks for your input!

Upvotes: 0

Views: 1145

Answers (1)

Kristian Bortnik
Kristian Bortnik

Reputation: 838

This is the SQL you are looking for:

SELECT LeadersID, Name + ', ' + Title FROM co_leaders ORDER BY Name

You need to do a concatenation of the column values (Name and Title), as opposed to selecting the columns separately.

EDIT: This is assuming that Name and Title are not nullable fields. If there is a NULL value in any of the concatenated fields, the end value will be NULL. In this case, you will need to use COALESCE (or an equivalent function) to define an alternative value. For example:

SELECT LeadersID, Name + ', ' + COALESCE(Title, 'Member') FROM co_leaders ORDER BY Name

Upvotes: 4

Related Questions