Need help separating values from a column and creating a new record
I have records where:
COMPANY NAME PART NUMBER MFG CODE CAPABILITY CODE
Acme LLC A123 1234A BC Repair OVH
I want to create a new record for each value in capability code and look like this::
COMPANY NAME PART NUMBER MFG CODE CAPABILITY CODE
Acme LLC A123 1234A BC
Acme LLC A123 1234A Repair
Acme LLC A123 1234A OVH
Book1.xlsx
COMPANY NAME PART NUMBER MFG CODE CAPABILITY CODE
Acme LLC A123 1234A BC Repair OVH
I want to create a new record for each value in capability code and look like this::
COMPANY NAME PART NUMBER MFG CODE CAPABILITY CODE
Acme LLC A123 1234A BC
Acme LLC A123 1234A Repair
Acme LLC A123 1234A OVH
Book1.xlsx
Sr. System Analyst
CERTIFIED EXPERT
with tbl as (
select 'Acme LLC ' COMPANY_NAME, 'A123' PART_NUMBER, '1234A' MFG_CODE, 'BC Repair OVH' CAPABILITY_CODE
), c as (
SELECT Split.a.value('.', 'VARCHAR(100)') AS CAPABILITY_CODE
FROM (SELECT
CAST ('<M>' + REPLACE([CAPABILITY_CODE], ' ', '</M><M>') + '</M>' AS XML) AS String
FROM tbl) AS A CROSS APPLY String.nodes ('/M') AS Split(a)
)
select tbl.COMPANY_NAME, tbl.PART_NUMBER, tbl.MFG_CODE, c.CAPABILITY_CODE from tbl, c
Mike, The most capability codes you will see in the capability_code column is BC REPAIR OVH. Does this help you?
Database and Application Developer
Yes, it is very helpful. Your answer probably could be stated like:
Min: 1 capability code
Max: 3 capability codes
When more than one, the capability codes are separated with a single blank space and there are no leading or trailing spaces.
If anything different than what has been stated above, we will be able to deal with it later. But for now, we proceed with these assumptions.
Mike
Min: 1 capability code
Max: 3 capability codes
When more than one, the capability codes are separated with a single blank space and there are no leading or trailing spaces.
If anything different than what has been stated above, we will be able to deal with it later. But for now, we proceed with these assumptions.
Mike