CODEWITHSUNDEEP
sql
procx
procx

Reputation: 53

Replace value in result by a specific value

I need to make a query to collect some data from a database via SQL. In this data there is 1 value used as collection value. This are ID's of courses given. Sometimes a course can be given about f.e. Office. But people can do a course there for word, excel, powerpoint... But this is all given in 1 course by 1 tutor. Still for statistics I need to know if they participated the course for Word, Excel, Powerpoint ...

Is it possible to replace values in the resultset? With this i mean something like this:

if value = courseValue ==> replace value with specific courseValue (I can get the value via a subquery)

I hope this makes my problem clear and i appriciate all the help!

Upvotes: 4

Views: 26951

Answers (2)

DeanOC
DeanOC

Reputation: 7282

You can use a case statement in your select to return something other than the course id that is on the row. For example: 

SELECT 
    field1 AS 'Name',
    CASE 
        WHEN field2 = 'Foo' 
            THEN 'Bar' 
        WHEN field2 = 'Lorem'
            THEN 'Ipsum'
        ELSE 'Some Value'
    END
    AS 'Type',
    field3 AS 'Description'
FROM table

Upvotes: 11

Hans Kesting
Hans Kesting

Reputation: 39284

If I understand you correctly, you will need something along the lines of this:

  • Create a new table with "courseID" and "replacementID" columns, fill it for the cases where there is a replacement
  • In your query do an outer join with this table over the courseID fields and also return the "replacementID", which can be null is there is no replacement
  • Use either the replacementID if it isn't null or the courseID

Upvotes: 0

Related Questions