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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply