July 7, 2005 at 1:45 pm
Using Access as our front end, we have a table consisting of Projects. These projects have fields "RC", "P1", and "ME". In these fields they either have "yes" or "no".
We have a form that searchs the projects based on criteria that they input. When they hit the search button, the results show up in a subform that is a datasheet. There are 7 columns (including Project #, Project Title, Project Estimated Cost, etc.) We are using a stored procedure to pull in these fields based on the search criteria. We would like to add another column to include the fields "RC", "P1", and "ME". What we had in mind is one column that showed which ones they had. For example: if a project only had a "yes" in the RC field, the column would simply show "RC"; but if a project had a "yes" in the RC and P1 fields, the column would show "RC, P1".
What would the stored procedure look like to pull in these fields into one column?
Our thoughts so far...:
DECLARE @Priority as varchar(25)
if RC = "Y"
@Priority = 'RC'
if P1 = "Y"
@Priority = @Priority + " " + 'P1'
...etc.
Any thoughts???
July 7, 2005 at 1:52 pm
Case when RC = 'Y' THEN 'RC, ' ELSE '' END +
Case when P1 = 'Y' THEN 'P1, ' ELSE '' END +
Case when ME = 'Y' THEN 'ME, ' ELSE '' END
As concat
just trim the last ', ' using a derived table or at the client side.
July 7, 2005 at 2:50 pm
and if those ACCESS fields are "yes"/"no" they are usually created in SQL Server as bit type and should that be the case just replace the " = 'Y' " in the above query to " = 1 "
Cheers!
* Noel
July 7, 2005 at 2:51 pm
And to -1 if it's tinyint, smallint, int or bigint (god forbid).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply