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

maximus1974
maximus1974 used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
HainKurtSr. System Analyst

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

Database and Application Developer
Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
maximus1974

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

Commented:
ID: 42087361 can handle up to 100!

did you check it?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial