We help IT Professionals succeed at work.

Need help separating values from a column and creating a new record

211 Views
Last Modified: 2017-04-11
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
Comment
Watch Question

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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

HainKurtSr. System Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Database and Application Developer
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Mike, The most capability codes you will see in the capability_code column is BC REPAIR OVH. Does this help you?
Mike EghtebasDatabase and Application Developer

Commented:
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
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
ID: 42087361 can handle up to 100!

did you check it?