0

I have a table that, for our purposes here, has 2 columns, the first that represents the group in the second column. It's not a conventional way to group data, but it's what I have to work with. Here is a test view of the table:

mfr_code  |  group
ABC       |  ABC,FFF,XYZ
DEF       |  DEF,GGG
GHI       |  GHI,PPP,RRR

I need to have a JOIN that links to the mfr_code column and then determines all of the individual mfr_codes in the second column.

The table this table would be joining to would look something like this:

from_catcode  |  partno  |  mfr_code
DORC          |  1234    |  ABC

Once joined, I need to be able to produce a table that looks like:

from_catcode  |  partno  |  mfr_code
DORC          |  1234    |  ABC
DORC          |  1234    |  FFF
DORC          |  1234    |  XYZ

I have been trying to figure out how to parse this multivalued column, but I fear a loop is impractical because we're talking about looping through each record of both tables, one inside the other, for tens of thousands of records.

Any insight or direction of where I can study up further on this would be helpful.

rlphilli
  • 111
  • 1
  • 4
  • 17
  • Because of the "unconventional" manner of grouping, your data are not even in first normal form. The reason that's unconventional is that it is all kinds of difficult to work with. – John Bollinger Sep 30 '15 at 17:37
  • Consult http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows for several alternatives for splitting up your individual rows into groups of rows; once that's done, you can perform a straightforward join and selection to obtain your result (alternatively, join, *then* split). Some of the alternatives can be performed via views, either static or inline. – John Bollinger Sep 30 '15 at 17:42
  • Thanks, John, for the heads up. I was trying to look for similar questions before I posted this one, but couldn't seem to find the right wording in order to find the post you referenced, but it is very helpful to what I was looking for. Thank you. – rlphilli Oct 01 '15 at 13:14

1 Answers1

1

You can split and join the table in a single statement. Try below code

    DECLARE @MrfDesc TABLE
(
    mrf_code varchar(10), groups varchar(50)
)
INSERT INTO @MrfDesc VALUES
('ABC','ABC,FFF,XYZ'),
('DEF','DEF,GGG'),
('GHI','GHI,PPP,RRR')

DECLARE @mrfMajor TABLE (from_catcode varchar(20) ,  partno INT  , mfr_code varchar(10))
INSERT INTO @MrfMajor VALUES('DORC',1234,'ABC')

SELECT MMajor.from_catcode, MMajor.partno, subset.Certs FROM @mrfMajor MMajor JOIN (
SELECT mrf_code,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(SELECT mrf_code,CAST('<XMLRoot><RowData>' + 
                        REPLACE(groups,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
        FROM   @MrfDesc)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n) ) subset 
ON subset.mrf_code = MMajor.mfr_code

SQL Fiddle demo

Anuj Tripathi
  • 2,251
  • 14
  • 18