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
maximus1974Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented: 
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 AnalystCommented:
with multiple rows

with tbl as (
select 'Acme LLC ' COMPANY_NAME, 'A123' PART_NUMBER, '1234A' MFG_CODE, 'BC Repair OVH' CAPABILITY_CODE
union
select 'XYZ LLC ' COMPANY_NAME, 'X123' PART_NUMBER, '1234B' MFG_CODE, 'XY Fix XYZ' CAPABILITY_CODE
), c as (
SELECT MFG_CODE, Split.a.value('.', 'VARCHAR(100)') AS CAPABILITY_CODE  
  FROM (
        SELECT MFG_CODE, 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 inner join c on tbl.MFG_CODE=c.MFG_CODE

Open in new window


result

COMPANY_NAME	PART_NUMBER	MFG_CODE	CAPABILITY_CODE
Acme LLC 	A123	1234A	BC
Acme LLC 	A123	1234A	Repair
Acme LLC 	A123	1234A	OVH
XYZ LLC 	X123	1234B	XY
XYZ LLC 	X123	1234B	Fix
XYZ LLC 	X123	1234B	XYZ

Open in new window

Mike EghtebasDatabase and Application DeveloperCommented:
What is a reasonable number of codes in CAPABILITY CODE column?

My plan of action is to find the number of the CAPABILITY CODEs in each row using a derived table sub query like:

use ee

DECLARE @temp TABLE ([COMPANY NAME] varchar(20), [PART NUMBER] varchar(10), [MFG CODE] varchar(10), [CAPABILITY CODE]  varchar(20));
DECLARE @tempOut TABLE ([COMPANY NAME] varchar(20), [PART NUMBER] varchar(10), [MFG CODE] varchar(10), [CAPABILITY CODE]  varchar(20));
insert into @temp([COMPANY NAME], [PART NUMBER], [MFG CODE], [CAPABILITY CODE]) values
('Acme LLC', 'A123', '1234A', 'BC Repair OVH');
 
select NoOfRows    -- this give 3 which is correct for BC Repair OVH
       --, case NoOfRows
      -- When 1 ...    
	From (
select len([CAPABILITY CODE]) - len(Replace([CAPABILITY CODE],' ', '')) + 1 NoOfRows
From @temp)d;

Open in new window


Then, starting from lines 9 and 10, we could write insert into @tempOut TABLE. So I need to know what are the max of  NoOfRows are to build my case when end statements?

I will be back on this this evening California time.

Mike

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Download eBook
maximus1974Author 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 DeveloperCommented:
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 AnalystCommented:
ID: 42087361 can handle up to 100!

did you check it?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.