Need to Derive 2 values in a CASE statement 1

[btturner]

Is it possible to derive 2 values in a CASE statement?

I need to INSERT multiple column values w/ a nested select using a Case Statement. (perhaps a SET statement w/in the CASE statement??)

TABLE_XYZ has 4 columns: Cust_No, City_Name, City_Abbr, City_Count

INSERT INTO TABLE_XYX
SELECT CustomerNo,
case when CityID = 44
then 'Los Angeles', 'LA'
else 'Other', 'Other'
end,
count(*)
from TABLE_1
GROUP BY CustomerNo, CityID

 

[JayKusch]

You may have to use 2 case statements. One for the city and then 1 for the abbrv.

Thanks

J. Kusch

 

[aroggeband]

It may be better to have a table containing cities
with 3 columns:
City_ID, CityName, CityCode
eg
44, Los Angeles, LA
etc.
City_id should be a primary key or constrained to be unique

then you could write:
INSERT INTO TABLE_XYX
SELECT T.CustomerNo,
isnull(C.CityName,'other'),
isnull(C.CityCode,'other'),
count(*)
from TABLE_1 T
left join Cities C
on C.City_ID = T.CityID
GROUP BY T.CustomerNo, T.CityID

this would look up the cities in a table. Advantages include that there is one central location and one spelling for each city, as well as avoiding cases for each field. It is also easier to add cities without risk of breaking TSQL code.
The left join makes sure that unknown city codes do not result in overlooking records. Any unknown cities come through as 'other' until the row is added for the city.



(bard+math)-->2b|^2b=?