CODEWITHSUNDEEP
sqlsql-servert-sql
user4481538
user4481538

Reputation:

Convert column to a different value based on another columns value

I have a table like

ID | Name | ProdID | Model | StudID
-----------------------------------
1  |  A   |   3    |  hey  |   6
2  |  B   |   4    |  he   |   7
2  |  C   |   5    |  hi   |   8

I need to make just Model and StudID values to N/A when ProdID is 4 and 5

ID | Name | ProdID | Model | StudID
-----------------------------------
1  |  A   |   3    |  hey  |   6
2  |  B   |   4    |  N/A  |   N/A
2  |  C   |   5    |  N/A  |   N/A

Here's a small sample of what I've done so far

SELECT      
     ID,
     Name,
     CASE 
          WHEN CONVERT(VARCHAR, ProdID) = 4 THEN CONVERT(VARCHAR, Model) = 'N/A'
     ..

Upvotes: 1

Views: 1029

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

First, you need to be quite careful about types.

  • Don't convert values to strings unless you have to.
  • When using convert() or cast() always include a length for varchar().

So:

SELECT ID, Name, ProdId,
       (CASE WHEN ProdId IN (4, 5) THEN Model ELSE 'N/A' END) as Model,
       (CASE WHEN ProdId IN (4, 5) THEN CONVERT(VARCHAR(255), StudId) ELSE 'N/A' END) as StudId
       ...

Note that you need conversion for StudId, assuming the value is an integer.

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this

 SELECT      
 ID,
 Name,
 ProdID,
 CASE 
      WHEN ProdID IN( 4,5) THEN 'N/A'
      ELSE CONVERT(VARCHAR, Model)
 END AS 'Model',
 CASE 
      WHEN ProdID IN( 4,5) THEN 'N/A'
      ELSE CONVERT(VARCHAR, StudID)
 END AS 'StudID'

Upvotes: 0

mohan111
mohan111

Reputation: 8865

select ID ,  Name ,  ProdID ,  
CASE WHEN ProdID IN (4,5) THEN 'N/A' ELSE Model END Model ,
CASE WHEN ProdID IN (4,5) THEN 'N/A' ELSE StudID END   StudID from @Table1

Upvotes: 0

Related Questions