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
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
Last Comment
HainKurt
8/22/2022 - Mon
HainKurt
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?
Mike Eghtebas
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.
Open in new window