Split one column into 2 columns based on the last character of the column-CodePudding

I have a column named region_no in a table. The column can hold 2 kinds of values - one set of values that end with the letter 'R' and the other that end with letter 'B'. This column must be split into 2 columns based on the last letter. The create table and insert of sample data is :

create table test_17Jan(Region_No varchar(8), Customer_Name varchar(20), City varchar(20), zip_code varchar(10) )

INSERT INTO test_17Jan VALUES ('101R', 'John Doe', 'Detroit', '48127')
INSERT INTO test_17Jan VALUES ('202B', 'John Doe', 'Detroit', '48127')
INSERT INTO test_17Jan VALUES ('201B', 'Tim Smith', 'Waunakee', '53597')

The desire output is :

----------------------------------------------------------------------------------
Customer_Name   | City          | zip_code  | Inside_Sales_Region   | B2B_Region
----------------------------------------------------------------------------------
John Doe        | Detroit       | 48127     |   101R                | 202B
----------------------------------------------------------------------------------
Tim Smith       | Waunakee      | 53597     |   NULL                | 201B
----------------------------------------------------------------------------------

I thought of pivot function, but that needs to have an aggregate. Is there a way to get the output in the above format? Any help will be appreciated. The code will run on SQL Server v15 .

CodePudding user response：

You can use conditional aggregation to get your desired results:

select Customer_Name, City, zip_code,
  Max(Inside_Sales_Region) Inside_Sales_Region,
  Max(B2B_Region) B2B_Region
from (
  select Customer_Name, City, zip_code,
    case when Right(Region_No,1) = 'R' then Region_No end Inside_Sales_Region,
    case when Right(Region_No,1) = 'B' then Region_No end B2B_Region
  from test_17Jan
)t
group by Customer_Name, City, zip_code;

CodePudding user response：

better option should be LEFT SELF JOIN. But this should also work.

SELECT 
CUSTOMER_NAME, CITY, ZIP_CODE,
CASE
    WHEN Region_No like '%R' THEN Region_No
    ELSE NULL AS Inside_Sales_Region,
CASE
    WHEN Region_No like '%B' THEN Region_No
    ELSE NULL AS B2B_Region

FROM test_17Jan;