Is it possible to combine fields and text in a select statement?
In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this:
SELECT DISTINCT GRPAS GroupName, "Year: " +YEAR + "Grade: " + GRDAS ShowMeFROM GE_DataWHERE (DIST = @.DIST)
I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets.
If this is possible, or there's a better way to do it, I'd love some more info.
Thanks!
-Mathminded
You could do it in the SELECT statement. This kind of formatting is generally done at the application/GUI layer. You need to use single quotes for strings. Also Year is a keyword so you use square brackets.
SELECT DISTINCT GRPAS GroupName, 'Year: ' + [YEAR] + 'Grade: ' + [GRD]AS ShowMeFROM GE_DataWHERE (DIST = @.DIST)
|||
I got it to work! On a whim I decided to try single quotes and that got me farther. The error it produced then led me to this page:
http://weblogs.foxite.com/andykramek/archive/2005/09/18/921.aspx
Then I realized I needed to change the type for one of the columns. Thus, I ended up with this SQL statement which works:
SELECT DISTINCT GRPAS GroupName,'Year: ' +YEAR +' and Grade: ' +CAST(GRDAS CHAR(2))AS ShowMeFROM GE_DataWHERE (DIST = @.DIST)|||
ndinakar:
You could do it in the SELECT statement. This kind of formatting is generally done at the application/GUI layer. You need to use single quotes for strings. Also Year is a keyword so you use square brackets.
SELECT DISTINCT GRPAS GroupName, 'Year: ' + [YEAR] + 'Grade: ' + [GRD]AS ShowMeFROM GE_DataWHERE (DIST = @.DIST)
Thanks, Dinakar! I thought I had figured it out quickly but you had it even faster! :-) Thanks for pointing out the keyword issue, also.
|||The output from my working statement is:
GroupName ShowMe
With the way my dropDownList is working, the user could select any of the first 3 choices, for instance, and end up with the same group. It would really be great if I could get this to output a single GroupName and combine the other information. For instance:
GroupName ShowMe
A Years: 0203,0304,0405 and Grades: 3,4,5
B Years: 0203,0304,0405 and Grades 4,5,6
etc...
Would that be really difficult to do? I may play around with it and see if I can get it using embeded select statements. I've never tried those before. In the off chance that I'm successful, I'll post my results. :-)
|||Again, this is the task that has to be done in the application layer. You have more string functions available in .NET to manipulate the strings than in SQL Server.|||
I thought it may make things easier if I changed how the information was displayed. Rather than try to fit all that info into the dropDownList using some complicated SQL query, I'd like to create a table with columns Group, Years, and Grades so the users can refer to that when choosing just the group letter from the dropDownList. Here's a sample of the table I'd like to display to the users:
GROUP
YEARS
GRADES
A
0203, 0304, 0405
3, 4, 5
B
0203, 0304, 0405
4, 5, 6
C
0203, 0304, 0405
5, 6, 7
D
0203, 0304, 0405, 0506
6, 7, 8, 9
The database table has the data stored like this:
GRP
YEAR
GRD
A
0203
3
A
0304
4
A
0405
5
B
0203
4
B
0304
5
B
0405
6
C
0203
5
C
0304
6
C
0405
7
D
0203
6
D
0304
7
D
0405
8
D
0506
9
I've tried a bunch of different things with the GridView in Visual Studio but I'm not meeting with success. Any advice would be appreciated.
Thanks!
-Mathminded
|||You can create a stored proc and a local table variable in it, get the values in the format you want into the table and do a select from the table at the end.
Check if this post helps in the concatenation:http://forums.asp.net/thread/1514443.aspx
No comments:
Post a Comment