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

4/10/2017

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

4/10/2017

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

4/10/2017

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

4/10/2017

ASKER

Mike, The most capability codes you will see in the capability_code column is BC REPAIR OVH. Does this help you?

Mike Eghtebas

4/10/2017

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

4/10/2017

ID: 42087361 can handle up to 100!

did you check it?