September 30, 2008 at 12:45 am
I have a table with columns Customer_ID,Name,Country in Customers Table. I want to SELECT columns Customer_ID,Name,Country from Customers Table and also a new column 'Region' to which hard coded values will be supplied in the SELECT statement.
I tried this way
select Customer_ID,Name,Country,'East' as Region, 'west' as Region, 'North' as Region, 'South' as Region from customer
Result Set:
Customer_ID Name Country Region Region Region Region
1 Pabc India East west North South
2 Pbcd USA East west North South
3 Pcde USA East west North South
4 Pdef India East west North South
But I need the result set as
Customer_ID Name Country Region
1 Pabc India East
2 Pbcd USA west
3 Pcde USA North
4 Pdef India South
Can anybody help me solve this.
Thanks in advance.
September 30, 2008 at 6:01 am
I am not sure what you are trying to do, but the following may help.
SELECT C.Customer_ID, C.[Name], C.Country, R.Region
FROM Countries C
    JOIN
    (
        SELECT 1, 'East' UNION ALL
        SELECT 2, 'West' UNION ALL
        SELECT 3, 'North' UNION ALL
        SELECT 4, 'South'
    ) R (Customer_ID, Region)
        ON C.Customer_ID = R.Customer_ID
September 30, 2008 at 6:01 am
You have to have a way to differentiate which value belongs with which row. I know this is probably not exactly what you're looking for, but something like this is what you have to do:
SELECT Customer_ID
,Name
,Country
,CASE WHEN Customer_ID =1
THEN 'North'
WHEN Customer_ID = 2
THEN 'South'
WHEN Customer_ID = 3
THEN 'East'
WHEN Customer_ID = 4
THEN 'West
ELSE 'Up'
END AS Region
FROM...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy