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
Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
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

Open in new window

SOLUTION
HainKurt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Mike Eghtebas

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
maximus1974

ASKER
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.

Mike
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
HainKurt

ID: 42087361 can handle up to 100!

did you check it?