asked on
SQL Query/View - Change record from numeric to specific text value??
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!
Thanks!
MiscellaneousSQL
Last Comment
Pawan Kumar
can you send few rows from the output of view and the view code ?
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]
,
CASE WHEN [1099 Type] = 1 THEN 'IE' ELSE 'Miscellaneous' END [1099 Type]
ASKER
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]()
VADCDTRO AS [Address Remit To], TEN99TYPE AS [1099 Type]
FROM dbo.PM00200
--------------------------
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
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
ASKER
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?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
ASKER
Great!!! Worked perfectly! Thank you, Pawan!
Welcome, glad to help as always. !!