Link to home
Start Free TrialLog in
Avatar of maximus1974
maximus1974

asked on

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
Avatar of HainKurt
HainKurt
Flag of Canada image

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

Open in new window

SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of maximus1974
maximus1974

ASKER

Mike, The most capability codes you will see in the capability_code column is BC REPAIR OVH. Does this help you?
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
ID: 42087361 can handle up to 100!

did you check it?