SQL Query/View - Change record from numeric to specific text value??

Jason Shaw
Jason Shaw used Ask the Experts™
on
I have a SQL view that I am modifying and attempting to add a new column 1099 Type. The data for this column is actually a 1 or 4. I need change those values to be specific text. IE when someone runs the view and sees 1099 Type, they should see Miscellaneous instead of a 4. I have am a beginner in sql and need some assistance!
Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
can you send few rows from the output of view and the view code ?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
You can add the new column like below. We need to use the CASE statement.

,
CASE WHEN [1099 Type] = 1 THEN 'IE' ELSE 'Miscellaneous' END [1099 Type]

Author

Commented:
SELECT     TOP (100) PERCENT VENDORID AS [Vendor ID], VENDNAME AS [Vendor Name], ADDRESS1 AS [Address 1], ADDRESS2 AS [Address 2], CITY, STATE, ZIPCODE AS [Zip Code], PHNUMBR1 AS [Phone Number 1], VADDCDPR AS [Address ID],
                  VADCDTRO AS [Address Remit To], TEN99TYPE AS [1099 Type]
FROM        dbo.PM00200
-------------------------------------------------------------------------------------------------------
Obfuscated partial view of output
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this --

SELECT     TOP (100) PERCENT VENDORID AS [Vendor ID], VENDNAME AS [Vendor Name], ADDRESS1 AS [Address 1], ADDRESS2 AS [Address 2], CITY, STATE, ZIPCODE AS [Zip Code], PHNUMBR1 AS [Phone Number 1], VADDCDPR AS [Address ID],
                  VADCDTRO AS [Address Remit To], TEN99TYPE AS [1099 Type]
,CASE WHEN TEN99TYPE  = 1 THEN 'IE' ELSE 'Miscellaneous' END [1099 Type]
FROM        dbo.PM00200

Author

Commented:
Ok, that works good with one exception...I have 2 columns that say 1099 Type and one of the columns now shows the text but the other is showing numeric values. I am trying to figure out where to eliminate the bad column in the query?
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Ok. Please use this -

SELECT     TOP (100) PERCENT VENDORID AS [Vendor ID], VENDNAME AS [Vendor Name], ADDRESS1 AS [Address 1], ADDRESS2 AS [Address 2], CITY, STATE, ZIPCODE AS [Zip Code], PHNUMBR1 AS [Phone Number 1], VADDCDPR AS [Address ID],
                  VADCDTRO AS [Address Remit To]
,CASE WHEN TEN99TYPE  = 1 THEN 'IE' ELSE 'Miscellaneous' END [1099 Type]
FROM        dbo.PM00200

Author

Commented:
qeuryoutput.png

Author

Commented:
Great!!! Worked perfectly! Thank you, Pawan!
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Welcome, glad to help as always. !!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial