January 31, 2016 at 3:43 pm
I posted this question earlier today and a couple of people responded but now the post no longer exists. I am not sure why it was deleted, so I am re-posting it.
I have a table that returns the following rows:
RBR Attribute Value
------- ---------- ----------
ONR XLOC ML
ONR XLOC MS
ONR XLOC ML
ONR XLOC MS
ONR CRE AA
ABC XLOC AR
ABC CRE BA
DDR XLOCMM
I am having a difficult time trying to pivot the results to look like this:
RBRXLOC CRE
------- ------- ---------
ONR MLAA
ONRMSAA
ABCARBA
DDRMMNULL
Here are the statements to create the table:
CREATE TABLE Test (
RBR VARCHAR(3) NULL,
Attribute VARCHAR(10) NULL,
Value VARCHAR(2)
)
INSERT INTO Test VALUES ('ONR','XLOC','ML')
INSERT INTO Test VALUES ('ONR','XLOC','MS')
INSERT INTO Test VALUES ('ONR','XLOC','ML')
INSERT INTO Test VALUES ('ONR','XLOC','MS')
INSERT INTO Test VALUES ('ONR','CRE','AA')
INSERT INTO Test VALUES ('ABC','XLOC','AR')
INSERT INTO Test VALUES ('ABC','CRE','BA')
INSERT INTO Test VALUES ('DDR','XLOC','MM')
February 1, 2016 at 4:24 am
select RBR,XLOC,CRE
FROM
(
select RBR,Attribute,Value from Test
) tbl
pivot(min(value) for Attribute in (XLOC,CRE)) as pvt
UNION
select RBR,XLOC,CRE
FROM
(
select RBR,Attribute,Value from Test
) tbl
pivot(max(value) for Attribute in (XLOC,CRE)) as pvt
February 1, 2016 at 6:54 am
ER...I am hesitant to mark that as a solution only because I don't believe it solves the issue if there are more than one duplicate. I am hoping to identify a query that would be able to handle various degrees of duplication, not just a single duplicate. That's probably my fault for not stating that in the original post. Do you know how this could be solved if the following data was used instead?
CREATE TABLE Test (
RBR VARCHAR(3) NULL,
Attribute VARCHAR(10) NULL,
Value VARCHAR(2)
)
INSERT INTO Test VALUES ('ONR','XLOC','ML')
INSERT INTO Test VALUES ('ONR','XLOC','MS')
INSERT INTO Test VALUES ('ONR','XLOC','MA')
INSERT INTO Test VALUES ('ONR','XLOC','ML')
INSERT INTO Test VALUES ('ONR','XLOC','MS')
INSERT INTO Test VALUES ('ONR','XLOC','MA')
INSERT INTO Test VALUES ('ONR','CRE','AA')
INSERT INTO Test VALUES ('ABC','XLOC','AR')
INSERT INTO Test VALUES ('ABC','CRE','BA')
INSERT INTO Test VALUES ('DDR','XLOC','MM')
February 1, 2016 at 7:44 am
SELECT t.RBR, t.Value, x.Value
FROM #Test t
OUTER APPLY (SELECT ti.Value FROM #Test ti WHERE ti.RBR = t.RBR AND ti.Attribute = 'CRE') x
WHERE t.Attribute = 'XLOC'
GROUP BY t.RBR, t.Value, x.Value
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 1, 2016 at 8:34 am
ChrisM...in your solution, if there were additional possible values in the Attribute column, would you need to have an OUTER APPLY for each of the possible attribute values?
February 1, 2016 at 8:46 am
Sorry, I totally misread the problem.
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = 'SELECT RBR, '
+ STUFF((SELECT ',' + CHAR(13) + CHAR(10)
+ 'MAX(CASE WHEN Attribute = ' + CHAR(39) + Attribute
+ CHAR(39) + ' THEN Value ELSE NULL END) AS ['
+ Attribute + ']'
FROM ( SELECT DISTINCT
Attribute
FROM #Test
) a
FOR XML PATH(''),
TYPE).value('.', 'NVARCHAR(MAX)'), 1, 3, '')
+ ' FROM #Test GROUP BY RBR;';
EXECUTE sp_executesql @SQL;
Returns: -
RBR CRE XLOC
---- ---- ----
ABC BA AR
DDR NULL MM
ONR AA MS
February 1, 2016 at 9:13 am
silverbullettruck (2/1/2016)
ChrisM...in your solution, if there were additional possible values in the Attribute column, would you need to have an OUTER APPLY for each of the possible attribute values?
Possibly not. Can you post up some sample data and expected results for clarification?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 1, 2016 at 9:48 am
I like ChrisM's approach but this may also help?
-- Get the distinct RBR \ XLOC values and OUTER to CRE values
-- Then do the same for RBR \ CRE
-- UNION the two sets will give distinct results.
SELECT X.RBR, X.Value as XLOC, C.Value as CRE
FROM (select distinct RBR, Value from Test where Attribute='XLOC') X
LEFT OUTER JOIN (select distinct RBR, Value from Test where Attribute='CRE') C
ON X.RBR = C.RBR
UNION
SELECT C.RBR, X.Value as XLOC, C.Value as CRE
FROM (select distinct RBR, Value from Test where Attribute='CRE') C
LEFT OUTER JOIN (select distinct RBR, Value from Test where Attribute='XLOC') X
ON C.RBR = X.RBR
February 1, 2016 at 10:05 am
ChrisM/ER...Here is the full dataset i am working with and then below that is the what i need the data to look like after it is pivoted.
CREATE TABLE #Test ( LOB VARCHAR(10), Attribute VARCHAR(10), Value VARCHAR(50) );
INSERT INTO #test
VALUES ( 'AGC', 'XLO', 'TP' );
INSERT INTO #test
VALUES ( 'BET', 'XLO', 'TP' );
INSERT INTO #test
VALUES ( 'CEX', 'XLO', 'EG' );
INSERT INTO #test
VALUES ( 'CMA', 'XLO', 'PS' );
INSERT INTO #test
VALUES ( 'CMN', 'XLO', 'PS' );
INSERT INTO #test
VALUES ( 'CMO', 'XLO', 'EG' );
INSERT INTO #test
VALUES ( 'COM', 'XLO', 'PS' );
INSERT INTO #test
VALUES ( 'COX', 'XLO', 'EG' );
INSERT INTO #test
VALUES ( 'FDI', 'XLO', 'FD' );
INSERT INTO #test
VALUES ( 'FEH', 'XLO', 'PS' );
INSERT INTO #test
VALUES ( 'FFB', 'XLO', 'TP' );
INSERT INTO #test
VALUES ( 'HAM', 'XLO', 'RO' );
INSERT INTO #test
VALUES ( 'HCO', 'XLO', 'PS' );
INSERT INTO #test
VALUES ( 'HFE', 'XLO', 'PS' );
INSERT INTO #test
VALUES ( 'HIN', 'XLO', 'IN' );
INSERT INTO #test
VALUES ( 'HMC', 'XLO', 'MM' );
INSERT INTO #test
VALUES ( 'IET', 'XLO', 'EI' );
INSERT INTO #test
VALUES ( 'IEX', 'XLO', 'EI' );
INSERT INTO #test
VALUES ( 'IND', 'XLO', 'IN' );
INSERT INTO #test
VALUES ( 'IOT', 'XLO', 'EI' );
INSERT INTO #test
VALUES ( 'IOX', 'XLO', 'EI' );
INSERT INTO #test
VALUES ( 'KOZ', 'XLO', 'RO' );
INSERT INTO #test
VALUES ( 'LLC', 'XLO', 'TP' );
INSERT INTO #test
VALUES ( 'MC1', 'XLO', 'SF' );
INSERT INTO #test
VALUES ( 'MDE', 'XLO', 'RO' );
INSERT INTO #test
VALUES ( 'MDS', 'XLO', 'MD' );
INSERT INTO #test
VALUES ( 'MDX', 'XLO', 'MD' );
INSERT INTO #test
VALUES ( 'MNS', 'XLO', 'ML' );
INSERT INTO #test
VALUES ( 'MNS', 'XLO', 'MS' );
INSERT INTO #test
VALUES ( 'MNS', 'XLO', 'ML' );
INSERT INTO #test
VALUES ( 'MNS', 'XLO', 'MS' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'XLO', 'ML' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'XLO', 'MS' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'XLO', 'ML' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'XLO', 'MS' );
INSERT INTO #test
VALUES ( 'MPC', 'XLO', 'MR' );
INSERT INTO #test
VALUES ( 'MSP', 'XLO', 'ML' );
INSERT INTO #test
VALUES ( 'MSP', 'XLO', 'MS' );
INSERT INTO #test
VALUES ( 'MSP', 'XLO', 'ML' );
INSERT INTO #test
VALUES ( 'MSP', 'XLO', 'MS' );
INSERT INTO #test
VALUES ( 'MWM', 'XLO', 'TP' );
INSERT INTO #test
VALUES ( 'N-A', 'XLO', 'RO' );
INSERT INTO #test
VALUES ( 'NPM', 'XLO', 'NP' );
INSERT INTO #test
VALUES ( 'NPP', 'XLO', 'NP' );
INSERT INTO #test
VALUES ( 'NPR', 'XLO', 'NP' );
INSERT INTO #test
VALUES ( 'NPS', 'XLO', 'NP' );
INSERT INTO #test
VALUES ( 'QNC', 'XLO', 'TP' );
INSERT INTO #test
VALUES ( 'QNS', 'XLO', 'TP' );
INSERT INTO #test
VALUES ( 'SVB', 'XLO', 'SF' );
INSERT INTO #test
VALUES ( 'SVM', 'XLO', 'SF' );
INSERT INTO #test
VALUES ( 'SVS', 'XLO', 'SF' );
INSERT INTO #test
VALUES ( 'SVV', 'XLO', 'SV' );
INSERT INTO #test
VALUES ( 'TPA', 'XLO', 'TP' );
INSERT INTO #test
VALUES ( 'WBS', 'XLO', 'RO' );
INSERT INTO #test
VALUES ( 'WHM', 'XLO', 'TP' );
INSERT INTO #test
VALUES ( 'ZTP', 'XLO', 'DN' );
INSERT INTO #test
VALUES ( 'ZUM', 'XLO', 'RP' );
INSERT INTO #test
VALUES ( 'ZZZ', 'XLO', 'ZZ' );
INSERT INTO #test
VALUES ( 'AGC', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'BET', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'CEX', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'CMA', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'CMN', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'CMO', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'COM', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'COX', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'FDI', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'FEH', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'FFB', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'HAM', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'HCO', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'HFE', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'HIN', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'HMC', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'IET', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'IEX', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'IND', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'IOT', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'IOX', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'KOZ', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'LLC', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'MC1', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'MDE', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'MDS', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'MDX', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'BPL', 'MSEL%' );
INSERT INTO #test
VALUES ( 'MNS', 'BPL', 'MSUP%' );
INSERT INTO #test
VALUES ( 'MNS', 'BPL', 'MSEL%' );
INSERT INTO #test
VALUES ( 'MNS', 'BPL', 'MSUP%' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'BPL', 'MNSEL%' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'BPL', 'MNSUP%' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'BPL', 'MNSEL%' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'BPL', 'MNSUP%' );
INSERT INTO #test
VALUES ( 'MPC', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'BPL', 'MSEL%' );
INSERT INTO #test
VALUES ( 'MSP', 'BPL', 'MSUP%' );
INSERT INTO #test
VALUES ( 'MSP', 'BPL', 'MSEL%' );
INSERT INTO #test
VALUES ( 'MSP', 'BPL', 'MSUP%' );
INSERT INTO #test
VALUES ( 'MWM', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'N-A', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'NPM', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'NPP', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'NPR', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'NPS', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'QNC', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'QNS', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'SVB', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'SVM', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'SVS', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'SVV', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'TPA', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'WBS', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'WHM', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'ZTP', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'ZUM', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'ZZZ', 'BPL', NULL );
INSERT INTO #test
VALUES ( 'AGC', 'CAR', 'TP' );
INSERT INTO #test
VALUES ( 'BET', 'CAR', 'TP' );
INSERT INTO #test
VALUES ( 'CEX', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'CMA', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'CMN', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'CMO', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'COM', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'COX', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'FDI', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'FEH', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'FFB', 'CAR', 'TP' );
INSERT INTO #test
VALUES ( 'HAM', 'CAR', 'XX' );
INSERT INTO #test
VALUES ( 'HCO', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'HFE', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'HIN', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'HMC', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'IET', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'IEX', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'IND', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'IOT', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'IOX', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'KOZ', 'CAR', 'XX' );
INSERT INTO #test
VALUES ( 'LLC', 'CAR', 'TP' );
INSERT INTO #test
VALUES ( 'MC1', 'CAR', 'TP' );
INSERT INTO #test
VALUES ( 'MDE', 'CAR', 'XX' );
INSERT INTO #test
VALUES ( 'MDS', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'MDX', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'MNS', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'MNS', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'MNS', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'MNS', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'MPC', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'MSP', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'MSP', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'MSP', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'MSP', 'CAR', 'HP' );
INSERT INTO #test
VALUES ( 'MWM', 'CAR', 'TP' );
INSERT INTO #test
VALUES ( 'N-A', 'CAR', 'XX' );
INSERT INTO #test
VALUES ( 'NPM', 'CAR', 'NP' );
INSERT INTO #test
VALUES ( 'NPP', 'CAR', 'NP' );
INSERT INTO #test
VALUES ( 'NPR', 'CAR', 'NP' );
INSERT INTO #test
VALUES ( 'NPS', 'CAR', 'NP' );
INSERT INTO #test
VALUES ( 'QNC', 'CAR', 'TP' );
INSERT INTO #test
VALUES ( 'QNS', 'CAR', 'TP' );
INSERT INTO #test
VALUES ( 'SVB', 'CAR', 'TP' );
INSERT INTO #test
VALUES ( 'SVM', 'CAR', 'TP' );
INSERT INTO #test
VALUES ( 'SVS', 'CAR', 'TP' );
INSERT INTO #test
VALUES ( 'SVV', 'CAR', 'TP' );
INSERT INTO #test
VALUES ( 'TPA', 'CAR', 'TP' );
INSERT INTO #test
VALUES ( 'WBS', 'CAR', 'XX' );
INSERT INTO #test
VALUES ( 'WHM', 'CAR', 'TP' );
INSERT INTO #test
VALUES ( 'ZTP', 'CAR', 'XX' );
INSERT INTO #test
VALUES ( 'ZUM', 'CAR', 'XX' );
INSERT INTO #test
VALUES ( 'ZZZ', 'CAR', 'XX' );
INSERT INTO #test
VALUES ( 'AGC', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'BET', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'CEX', 'RA', 'COM' );
INSERT INTO #test
VALUES ( 'CMA', 'RA', 'CMN' );
INSERT INTO #test
VALUES ( 'CMN', 'RA', 'CMN' );
INSERT INTO #test
VALUES ( 'CMO', 'RA', 'CMN' );
INSERT INTO #test
VALUES ( 'COM', 'RA', 'COM' );
INSERT INTO #test
VALUES ( 'COX', 'RA', 'COM' );
INSERT INTO #test
VALUES ( 'FDI', 'RA', 'FDI' );
INSERT INTO #test
VALUES ( 'FEH', 'RA', 'COM' );
INSERT INTO #test
VALUES ( 'FFB', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'HAM', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'HCO', 'RA', 'HOA' );
INSERT INTO #test
VALUES ( 'HFE', 'RA', 'HOA' );
INSERT INTO #test
VALUES ( 'HIN', 'RA', 'HOA' );
INSERT INTO #test
VALUES ( 'HMC', 'RA', 'HOA' );
INSERT INTO #test
VALUES ( 'IET', 'RA', 'IND' );
INSERT INTO #test
VALUES ( 'IEX', 'RA', 'IND' );
INSERT INTO #test
VALUES ( 'IND', 'RA', 'IND' );
INSERT INTO #test
VALUES ( 'IOT', 'RA', 'IND' );
INSERT INTO #test
VALUES ( 'IOX', 'RA', 'IND' );
INSERT INTO #test
VALUES ( 'KOZ', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'LLC', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'MC1', 'RA', 'SGH' );
INSERT INTO #test
VALUES ( 'MDE', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'MDS', 'RA', 'MDS' );
INSERT INTO #test
VALUES ( 'MDX', 'RA', 'MDX' );
INSERT INTO #test
VALUES ( 'MNS', 'RA', 'MSP' );
INSERT INTO #test
VALUES ( 'MNS', 'RA', 'MSP' );
INSERT INTO #test
VALUES ( 'MNS', 'RA', 'MSP' );
INSERT INTO #test
VALUES ( 'MNS', 'RA', 'MSP' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'RA', 'MSP' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'RA', 'MSP' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'RA', 'MSP' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'RA', 'MSP' );
INSERT INTO #test
VALUES ( 'MPC', 'RA', 'MPC' );
INSERT INTO #test
VALUES ( 'MSP', 'RA', 'MSP' );
INSERT INTO #test
VALUES ( 'MSP', 'RA', 'MSP' );
INSERT INTO #test
VALUES ( 'MSP', 'RA', 'MSP' );
INSERT INTO #test
VALUES ( 'MSP', 'RA', 'MSP' );
INSERT INTO #test
VALUES ( 'MWM', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'N-A', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'NPM', 'RA', 'NPM' );
INSERT INTO #test
VALUES ( 'NPP', 'RA', 'NPP' );
INSERT INTO #test
VALUES ( 'NPR', 'RA', 'NPR' );
INSERT INTO #test
VALUES ( 'NPS', 'RA', 'NPS' );
INSERT INTO #test
VALUES ( 'QNC', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'QNS', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'SVB', 'RA', 'SGH' );
INSERT INTO #test
VALUES ( 'SVM', 'RA', 'SGH' );
INSERT INTO #test
VALUES ( 'SVS', 'RA', 'SGH' );
INSERT INTO #test
VALUES ( 'SVV', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'TPA', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'WBS', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'WHM', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'ZTP', 'RA', 'DNR' );
INSERT INTO #test
VALUES ( 'ZUM', 'RA', 'OTH' );
INSERT INTO #test
VALUES ( 'ZZZ', 'RA', '---' );
INSERT INTO #test
VALUES ( 'AGC', 'ARA', 'TPA' );
INSERT INTO #test
VALUES ( 'BET', 'ARA', 'TPA' );
INSERT INTO #test
VALUES ( 'CEX', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'CMA', 'ARA', 'COM' );
INSERT INTO #test
VALUES ( 'CMN', 'ARA', 'COM' );
INSERT INTO #test
VALUES ( 'CMO', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'COM', 'ARA', 'COM' );
INSERT INTO #test
VALUES ( 'COX', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'FDI', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'FEH', 'ARA', 'COM' );
INSERT INTO #test
VALUES ( 'FFB', 'ARA', 'TPA' );
INSERT INTO #test
VALUES ( 'HAM', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'HCO', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'HFE', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'HIN', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'HMC', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'IET', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'IEX', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'IND', 'ARA', 'IND' );
INSERT INTO #test
VALUES ( 'IOT', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'IOX', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'KOZ', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'LLC', 'ARA', 'TPA' );
INSERT INTO #test
VALUES ( 'MC1', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MDE', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MDS', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MDX', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MPC', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'MWM', 'ARA', 'TPA' );
INSERT INTO #test
VALUES ( 'N-A', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'NPM', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'NPP', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'NPR', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'NPS', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'QNC', 'ARA', 'TPA' );
INSERT INTO #test
VALUES ( 'QNS', 'ARA', 'TPA' );
INSERT INTO #test
VALUES ( 'SVB', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'SVM', 'ARA', 'SH' );
INSERT INTO #test
VALUES ( 'SVS', 'ARA', 'SH' );
INSERT INTO #test
VALUES ( 'SVV', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'TPA', 'ARA', 'TPA' );
INSERT INTO #test
VALUES ( 'WBS', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'WHM', 'ARA', 'TPA' );
INSERT INTO #test
VALUES ( 'ZTP', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'ZUM', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'ZZZ', 'ARA', NULL );
INSERT INTO #test
VALUES ( 'AGC', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'BET', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'CEX', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'CMA', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'CMN', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'CMO', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'COM', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'COX', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'FDI', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'FEH', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'FFB', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'HAM', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'HCO', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'HFE', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'HIN', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'HMC', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'IET', 'PT', 'S' );
INSERT INTO #test
VALUES ( 'IEX', 'PT', 'S' );
INSERT INTO #test
VALUES ( 'IND', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'IOT', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'IOX', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'KOZ', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'LLC', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'MC1', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'MDE', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'MDS', 'PT', 'X' );
INSERT INTO #test
VALUES ( 'MDX', 'PT', 'X' );
INSERT INTO #test
VALUES ( 'MNS', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'MNS', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'MNS', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'MNS', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'MPC', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'MSP', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'MSP', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'MSP', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'MSP', 'PT', 'I' );
INSERT INTO #test
VALUES ( 'MWM', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'N-A', 'PT', 'X' );
INSERT INTO #test
VALUES ( 'NPM', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'NPP', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'NPR', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'NPS', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'QNC', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'QNS', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'SVB', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'SVM', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'SVS', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'SVV', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'TPA', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'WBS', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'WHM', 'PT', 'C' );
INSERT INTO #test
VALUES ( 'ZTP', 'PT', 'X' );
INSERT INTO #test
VALUES ( 'ZUM', 'PT', 'X' );
INSERT INTO #test
VALUES ( 'ZZZ', 'PT', 'X' );
INSERT INTO #test
VALUES ( 'AGC', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'BET', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'CEX', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'CMA', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'CMN', 'COCM', 'Y' );
INSERT INTO #test
VALUES ( 'CMO', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'COM', 'COCM', 'Y' );
INSERT INTO #test
VALUES ( 'COX', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'FDI', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'FEH', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'FFB', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'HAM', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'HCO', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'HFE', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'HIN', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'HMC', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'IET', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'IEX', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'IND', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'IOT', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'IOX', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'KOZ', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'LLC', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MC1', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MDE', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MDS', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MDX', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MPC', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'MWM', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'N-A', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'NPM', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'NPP', 'COCM', '?' );
INSERT INTO #test
VALUES ( 'NPR', 'COCM', '?' );
INSERT INTO #test
VALUES ( 'NPS', 'COCM', '?' );
INSERT INTO #test
VALUES ( 'QNC', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'QNS', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'SVB', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'SVM', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'SVS', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'SVV', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'TPA', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'WBS', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'WHM', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'ZTP', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'ZUM', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'ZZZ', 'COCM', 'N' );
INSERT INTO #test
VALUES ( 'AGC', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'BET', 'CBRAM', 'Y' );
INSERT INTO #test
VALUES ( 'CEX', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'CMA', 'CBRAM', 'Y' );
INSERT INTO #test
VALUES ( 'CMN', 'CBRAM', 'Y' );
INSERT INTO #test
VALUES ( 'CMO', 'CBRAM', 'Y' );
INSERT INTO #test
VALUES ( 'COM', 'CBRAM', 'Y' );
INSERT INTO #test
VALUES ( 'COX', 'CBRAM', 'Y' );
INSERT INTO #test
VALUES ( 'FDI', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'FEH', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'FFB', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'HAM', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'HCO', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'HFE', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'HIN', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'HMC', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'IET', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'IEX', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'IND', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'IOT', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'IOX', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'KOZ', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'LLC', 'CBRAM', 'Y' );
INSERT INTO #test
VALUES ( 'MC1', 'CBRAM', 'Y' );
INSERT INTO #test
VALUES ( 'MDE', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MDS', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MDX', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MPC', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'MWM', 'CBRAM', 'Y' );
INSERT INTO #test
VALUES ( 'N-A', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'NPM', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'NPP', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'NPR', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'NPS', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'QNC', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'QNS', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'SVB', 'CBRAM', 'Y' );
INSERT INTO #test
VALUES ( 'SVM', 'CBRAM', 'Y' );
INSERT INTO #test
VALUES ( 'SVS', 'CBRAM', 'Y' );
INSERT INTO #test
VALUES ( 'SVV', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'TPA', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'WBS', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'WHM', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'ZTP', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'ZUM', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'ZZZ', 'CBRAM', 'N' );
INSERT INTO #test
VALUES ( 'AGC', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'BET', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'CEX', 'EFI', 'KZF' );
INSERT INTO #test
VALUES ( 'CMA', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'CMN', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'CMO', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'COM', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'COX', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'FDI', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'FEH', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'FFB', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'HAM', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'HCO', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'HFE', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'HIN', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'HMC', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'IET', 'EFI', 'KZF' );
INSERT INTO #test
VALUES ( 'IEX', 'EFI', 'KZF' );
INSERT INTO #test
VALUES ( 'IND', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'IOT', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'IOX', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'KOZ', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'LLC', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'MC1', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'MDE', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'MDS', 'EFI', 'LZC' );
INSERT INTO #test
VALUES ( 'MDX', 'EFI', 'LZC' );
INSERT INTO #test
VALUES ( 'MNS', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'MNS', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'MNS', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'MNS', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'MPC', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'MSP', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'MSP', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'MSP', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'MSP', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'MWM', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'N-A', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'NPM', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'NPP', 'EFI', 'NDP' );
INSERT INTO #test
VALUES ( 'NPR', 'EFI', 'NDP' );
INSERT INTO #test
VALUES ( 'NPS', 'EFI', 'NDP' );
INSERT INTO #test
VALUES ( 'QNC', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'QNS', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'SVB', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'SVM', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'SVS', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'SVV', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'TPA', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'WBS', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'WHM', 'EFI', 'SVH' );
INSERT INTO #test
VALUES ( 'ZTP', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'ZUM', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'ZZZ', 'EFI', 'X' );
INSERT INTO #test
VALUES ( 'AGC', 'DP', '1' );
INSERT INTO #test
VALUES ( 'BET', 'DP', '1' );
INSERT INTO #test
VALUES ( 'CEX', 'DP', '1' );
INSERT INTO #test
VALUES ( 'CMA', 'DP', '1' );
INSERT INTO #test
VALUES ( 'CMN', 'DP', '1' );
INSERT INTO #test
VALUES ( 'CMO', 'DP', '1' );
INSERT INTO #test
VALUES ( 'COM', 'DP', '1' );
INSERT INTO #test
VALUES ( 'COX', 'DP', '1' );
INSERT INTO #test
VALUES ( 'FDI', 'DP', '1' );
INSERT INTO #test
VALUES ( 'FEH', 'DP', '1' );
INSERT INTO #test
VALUES ( 'FFB', 'DP', '1' );
INSERT INTO #test
VALUES ( 'HAM', 'DP', '1' );
INSERT INTO #test
VALUES ( 'HCO', 'DP', '2' );
INSERT INTO #test
VALUES ( 'HFE', 'DP', '2' );
INSERT INTO #test
VALUES ( 'HIN', 'DP', '2' );
INSERT INTO #test
VALUES ( 'HMC', 'DP', '2' );
INSERT INTO #test
VALUES ( 'IET', 'DP', '3' );
INSERT INTO #test
VALUES ( 'IEX', 'DP', '1' );
INSERT INTO #test
VALUES ( 'IND', 'DP', '1' );
INSERT INTO #test
VALUES ( 'IOT', 'DP', '3' );
INSERT INTO #test
VALUES ( 'IOX', 'DP', '1' );
INSERT INTO #test
VALUES ( 'KOZ', 'DP', '1' );
INSERT INTO #test
VALUES ( 'LLC', 'DP', '1' );
INSERT INTO #test
VALUES ( 'MC1', 'DP', '1' );
INSERT INTO #test
VALUES ( 'MDE', 'DP', '1' );
INSERT INTO #test
VALUES ( 'MDS', 'DP', '1' );
INSERT INTO #test
VALUES ( 'MDX', 'DP', '1' );
INSERT INTO #test
VALUES ( 'MNS', 'DP', '4' );
INSERT INTO #test
VALUES ( 'MNS', 'DP', '4' );
INSERT INTO #test
VALUES ( 'MNS', 'DP', '4' );
INSERT INTO #test
VALUES ( 'MNS', 'DP', '4' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'DP', '4' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'DP', '4' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'DP', '4' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'DP', '4' );
INSERT INTO #test
VALUES ( 'MPC', 'DP', '1' );
INSERT INTO #test
VALUES ( 'MSP', 'DP', '4' );
INSERT INTO #test
VALUES ( 'MSP', 'DP', '4' );
INSERT INTO #test
VALUES ( 'MSP', 'DP', '4' );
INSERT INTO #test
VALUES ( 'MSP', 'DP', '4' );
INSERT INTO #test
VALUES ( 'MWM', 'DP', '1' );
INSERT INTO #test
VALUES ( 'N-A', 'DP', '1' );
INSERT INTO #test
VALUES ( 'NPM', 'DP', '1' );
INSERT INTO #test
VALUES ( 'NPP', 'DP', '1' );
INSERT INTO #test
VALUES ( 'NPR', 'DP', '1' );
INSERT INTO #test
VALUES ( 'NPS', 'DP', '1' );
INSERT INTO #test
VALUES ( 'QNC', 'DP', '1' );
INSERT INTO #test
VALUES ( 'QNS', 'DP', '1' );
INSERT INTO #test
VALUES ( 'SVB', 'DP', '1' );
INSERT INTO #test
VALUES ( 'SVM', 'DP', '1' );
INSERT INTO #test
VALUES ( 'SVS', 'DP', '1' );
INSERT INTO #test
VALUES ( 'SVV', 'DP', '1' );
INSERT INTO #test
VALUES ( 'TPA', 'DP', '1' );
INSERT INTO #test
VALUES ( 'WBS', 'DP', '1' );
INSERT INTO #test
VALUES ( 'WHM', 'DP', '1' );
INSERT INTO #test
VALUES ( 'ZTP', 'DP', '1' );
INSERT INTO #test
VALUES ( 'ZUM', 'DP', '1' );
INSERT INTO #test
VALUES ( 'ZZZ', 'DP', '1' );
INSERT INTO #test
VALUES ( 'AGC', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'BET', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'CEX', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'CMA', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'CMN', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'CMO', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'COM', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'COX', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'FDI', 'CVG', NULL );
INSERT INTO #test
VALUES ( 'FEH', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'FFB', 'CVG', NULL );
INSERT INTO #test
VALUES ( 'HAM', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'HCO', 'CVG', '109' );
INSERT INTO #test
VALUES ( 'HFE', 'CVG', '109' );
INSERT INTO #test
VALUES ( 'HIN', 'CVG', '109' );
INSERT INTO #test
VALUES ( 'HMC', 'CVG', '109' );
INSERT INTO #test
VALUES ( 'IET', 'CVG', '112' );
INSERT INTO #test
VALUES ( 'IEX', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'IND', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'IOT', 'CVG', '112' );
INSERT INTO #test
VALUES ( 'IOX', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'KOZ', 'CVG', NULL );
INSERT INTO #test
VALUES ( 'LLC', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'MC1', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'MDE', 'CVG', NULL );
INSERT INTO #test
VALUES ( 'MDS', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'MDX', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'MNS', 'CVG', '111' );
INSERT INTO #test
VALUES ( 'MNS', 'CVG', '111' );
INSERT INTO #test
VALUES ( 'MNS', 'CVG', '111' );
INSERT INTO #test
VALUES ( 'MNS', 'CVG', '111' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CVG', '111' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CVG', '111' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CVG', '111' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CVG', '111' );
INSERT INTO #test
VALUES ( 'MPC', 'CVG', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'CVG', '111' );
INSERT INTO #test
VALUES ( 'MSP', 'CVG', '111' );
INSERT INTO #test
VALUES ( 'MSP', 'CVG', '111' );
INSERT INTO #test
VALUES ( 'MSP', 'CVG', '111' );
INSERT INTO #test
VALUES ( 'MWM', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'N-A', 'CVG', NULL );
INSERT INTO #test
VALUES ( 'NPM', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'NPP', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'NPR', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'NPS', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'QNC', 'CVG', NULL );
INSERT INTO #test
VALUES ( 'QNS', 'CVG', NULL );
INSERT INTO #test
VALUES ( 'SVB', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'SVM', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'SVS', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'SVV', 'CVG', '0' );
INSERT INTO #test
VALUES ( 'TPA', 'CVG', NULL );
INSERT INTO #test
VALUES ( 'WBS', 'CVG', NULL );
INSERT INTO #test
VALUES ( 'WHM', 'CVG', NULL );
INSERT INTO #test
VALUES ( 'ZTP', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'ZUM', 'CVG', '108' );
INSERT INTO #test
VALUES ( 'ZZZ', 'CVG', NULL );
INSERT INTO #test
VALUES ( 'AGC', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'BET', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'CEX', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'CMA', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'CMN', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'CMO', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'COM', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'COX', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'FDI', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'FEH', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'FFB', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'HAM', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'HCO', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'HFE', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'HIN', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'HMC', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'IET', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'IEX', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'IND', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'IOT', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'IOX', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'KOZ', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'LLC', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'MC1', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'MDE', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'MDS', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'MDX', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'MNS', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'MPC', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'MWM', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'N-A', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'NPM', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'NPP', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'NPR', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'NPS', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'QNC', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'QNS', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'SVB', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'SVM', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'SVS', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'SVV', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'TPA', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'WBS', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'WHM', 'OP', 'Y' );
INSERT INTO #test
VALUES ( 'ZTP', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'ZUM', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'ZZZ', 'OP', 'N' );
INSERT INTO #test
VALUES ( 'AGC', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'BET', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'CEX', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'CMA', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'CMN', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'CMO', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'COM', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'COX', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'FDI', 'HX', 'N' );
INSERT INTO #test
VALUES ( 'FEH', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'FFB', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'HAM', 'HX', 'N' );
INSERT INTO #test
VALUES ( 'HCO', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'HFE', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'HIN', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'HMC', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'IET', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'IEX', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'IND', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'IOT', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'IOX', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'KOZ', 'HX', 'N' );
INSERT INTO #test
VALUES ( 'LLC', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'MC1', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'MDE', 'HX', 'N' );
INSERT INTO #test
VALUES ( 'MDS', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'MDX', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'MNS', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'MNS', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'MNS', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'MNS', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'HX', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'HX', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'HX', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'HX', 'N' );
INSERT INTO #test
VALUES ( 'MPC', 'HX', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'MSP', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'MSP', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'MSP', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'MWM', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'N-A', 'HX', 'N' );
INSERT INTO #test
VALUES ( 'NPM', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'NPP', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'NPR', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'NPS', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'QNC', 'HX', 'N' );
INSERT INTO #test
VALUES ( 'QNS', 'HX', 'N' );
INSERT INTO #test
VALUES ( 'SVB', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'SVM', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'SVS', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'SVV', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'TPA', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'WBS', 'HX', 'N' );
INSERT INTO #test
VALUES ( 'WHM', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'ZTP', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'ZUM', 'HX', 'Y' );
INSERT INTO #test
VALUES ( 'ZZZ', 'HX', 'N' );
INSERT INTO #test
VALUES ( 'AGC', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'BET', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'CEX', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'CMA', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'CMN', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'CMO', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'COM', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'COX', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'FDI', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'FEH', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'FFB', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'HAM', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'HCO', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'HFE', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'HIN', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'HMC', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'IET', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'IEX', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'IND', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'IOT', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'IOX', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'KOZ', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'LLC', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MC1', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MDE', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MDS', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MDX', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MPC', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'MWM', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'N-A', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'NPM', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'NPP', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'NPR', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'NPS', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'QNC', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'QNS', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'SVB', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'SVM', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'SVS', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'SVV', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'TPA', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'WBS', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'WHM', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'ZTP', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'ZUM', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'ZZZ', 'MHPD', NULL );
INSERT INTO #test
VALUES ( 'AGC', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'BET', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'CEX', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'CMA', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'CMN', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'CMO', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'COM', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'COX', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'FDI', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'FEH', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'FFB', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'HAM', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'HCO', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'HFE', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'HIN', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'HMC', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'IET', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'IEX', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'IND', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'IOT', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'IOX', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'KOZ', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'LLC', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MC1', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MDE', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MDS', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MDX', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MPC', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'MWM', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'N-A', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'NPM', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'NPP', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'NPR', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'NPS', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'QNC', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'QNS', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'SVB', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'SVM', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'SVS', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'SVV', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'TPA', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'WBS', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'WHM', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'ZTP', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'ZUM', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'ZZZ', 'SECTION', NULL );
INSERT INTO #test
VALUES ( 'AGC', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'BET', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'CEX', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'CMA', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'CMN', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'CMO', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'COM', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'COX', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'FDI', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'FEH', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'FFB', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'HAM', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'HCO', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'HFE', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'HIN', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'HMC', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'IET', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'IEX', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'IND', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'IOT', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'IOX', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'KOZ', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'LLC', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MC1', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MDE', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MDS', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MDX', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MPC', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'MWM', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'N-A', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'NPM', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'NPP', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'NPR', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'NPS', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'QNC', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'QNS', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'SVB', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'SVM', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'SVS', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'SVV', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'TPA', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'WBS', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'WHM', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'ZTP', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'ZUM', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'ZZZ', 'COBA', NULL );
INSERT INTO #test
VALUES ( 'AGC', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'BET', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'CEX', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'CMA', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'CMN', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'CMO', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'COM', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'COX', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'FDI', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'FEH', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'FFB', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'HAM', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'HCO', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'HFE', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'HIN', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'HMC', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'IET', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'IEX', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'IND', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'IOT', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'IOX', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'KOZ', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'LLC', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MC1', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MDE', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MDS', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MDX', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MPC', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'MWM', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'N-A', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'NPM', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'NPP', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'NPR', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'NPS', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'QNC', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'QNS', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'SVB', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'SVM', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'SVS', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'SVV', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'TPA', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'WBS', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'WHM', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'ZTP', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'ZUM', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'ZZZ', 'HMS', NULL );
INSERT INTO #test
VALUES ( 'AGC', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'BET', 'BDM', 'Y' );
INSERT INTO #test
VALUES ( 'CEX', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'CMA', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'CMN', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'CMO', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'COM', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'COX', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'FDI', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'FEH', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'FFB', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'HAM', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'HCO', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'HFE', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'HIN', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'HMC', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'IET', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'IEX', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'IND', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'IOT', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'IOX', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'KOZ', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'LLC', 'BDM', 'Y' );
INSERT INTO #test
VALUES ( 'MC1', 'BDM', 'S' );
INSERT INTO #test
VALUES ( 'MDE', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'MDS', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'MDX', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'MPC', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'MWM', 'BDM', 'Y' );
INSERT INTO #test
VALUES ( 'N-A', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'NPM', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'NPP', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'NPR', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'NPS', 'BDM', 'N' );
INSERT INTO #test
VALUES ( 'QNC', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'QNS', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'SVB', 'BDM', 'S' );
INSERT INTO #test
VALUES ( 'SVM', 'BDM', 'S' );
INSERT INTO #test
VALUES ( 'SVS', 'BDM', 'S' );
INSERT INTO #test
VALUES ( 'SVV', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'TPA', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'WBS', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'WHM', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'ZTP', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'ZUM', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'ZZZ', 'BDM', NULL );
INSERT INTO #test
VALUES ( 'AGC', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'BET', 'MMM', 'Y' );
INSERT INTO #test
VALUES ( 'CEX', 'MMM', 'N' );
INSERT INTO #test
VALUES ( 'CMA', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'CMN', 'MMM', 'N' );
INSERT INTO #test
VALUES ( 'CMO', 'MMM', 'N' );
INSERT INTO #test
VALUES ( 'COM', 'MMM', 'N' );
INSERT INTO #test
VALUES ( 'COX', 'MMM', 'N' );
INSERT INTO #test
VALUES ( 'FDI', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'FEH', 'MMM', 'N' );
INSERT INTO #test
VALUES ( 'FFB', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'HAM', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'HCO', 'MMM', 'N' );
INSERT INTO #test
VALUES ( 'HFE', 'MMM', 'N' );
INSERT INTO #test
VALUES ( 'HIN', 'MMM', 'N' );
INSERT INTO #test
VALUES ( 'HMC', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'IET', 'MMM', 'N' );
INSERT INTO #test
VALUES ( 'IEX', 'MMM', 'N' );
INSERT INTO #test
VALUES ( 'IND', 'MMM', 'N' );
INSERT INTO #test
VALUES ( 'IOT', 'MMM', 'N' );
INSERT INTO #test
VALUES ( 'IOX', 'MMM', 'N' );
INSERT INTO #test
VALUES ( 'KOZ', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'LLC', 'MMM', 'Y' );
INSERT INTO #test
VALUES ( 'MC1', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MDE', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MDS', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MDX', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MPC', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'MWM', 'MMM', 'Y' );
INSERT INTO #test
VALUES ( 'N-A', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'NPM', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'NPP', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'NPR', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'NPS', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'QNC', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'QNS', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'SVB', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'SVM', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'SVS', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'SVV', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'TPA', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'WBS', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'WHM', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'ZTP', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'ZUM', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'ZZZ', 'MMM', NULL );
INSERT INTO #test
VALUES ( 'AGC', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'BET', 'ANM', 'Y' );
INSERT INTO #test
VALUES ( 'CEX', 'ANM', 'C' );
INSERT INTO #test
VALUES ( 'CMA', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'CMN', 'ANM', 'C' );
INSERT INTO #test
VALUES ( 'CMO', 'ANM', 'C' );
INSERT INTO #test
VALUES ( 'COM', 'ANM', 'C' );
INSERT INTO #test
VALUES ( 'COX', 'ANM', 'C' );
INSERT INTO #test
VALUES ( 'FDI', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'FEH', 'ANM', 'F' );
INSERT INTO #test
VALUES ( 'FFB', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'HAM', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'HCO', 'ANM', 'H' );
INSERT INTO #test
VALUES ( 'HFE', 'ANM', 'F' );
INSERT INTO #test
VALUES ( 'HIN', 'ANM', 'H' );
INSERT INTO #test
VALUES ( 'HMC', 'ANM', 'M' );
INSERT INTO #test
VALUES ( 'IET', 'ANM', 'C' );
INSERT INTO #test
VALUES ( 'IEX', 'ANM', 'C' );
INSERT INTO #test
VALUES ( 'IND', 'ANM', 'C' );
INSERT INTO #test
VALUES ( 'IOT', 'ANM', 'C' );
INSERT INTO #test
VALUES ( 'IOX', 'ANM', 'C' );
INSERT INTO #test
VALUES ( 'KOZ', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'LLC', 'ANM', 'Y' );
INSERT INTO #test
VALUES ( 'MC1', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'MDE', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'MDS', 'ANM', 'N' );
INSERT INTO #test
VALUES ( 'MDX', 'ANM', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'ANM', 'M' );
INSERT INTO #test
VALUES ( 'MNS', 'ANM', 'M' );
INSERT INTO #test
VALUES ( 'MNS', 'ANM', 'M' );
INSERT INTO #test
VALUES ( 'MNS', 'ANM', 'M' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'MPC', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'ANM', 'M' );
INSERT INTO #test
VALUES ( 'MSP', 'ANM', 'M' );
INSERT INTO #test
VALUES ( 'MSP', 'ANM', 'M' );
INSERT INTO #test
VALUES ( 'MSP', 'ANM', 'M' );
INSERT INTO #test
VALUES ( 'MWM', 'ANM', 'Y' );
INSERT INTO #test
VALUES ( 'N-A', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'NPM', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'NPP', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'NPR', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'NPS', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'QNC', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'QNS', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'SVB', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'SVM', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'SVS', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'SVV', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'TPA', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'WBS', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'WHM', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'ZTP', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'ZUM', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'ZZZ', 'ANM', NULL );
INSERT INTO #test
VALUES ( 'AGC', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'BET', 'CSM', 'Y' );
INSERT INTO #test
VALUES ( 'CEX', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'CMA', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'CMN', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'CMO', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'COM', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'COX', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'FDI', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'FEH', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'FFB', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'HAM', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'HCO', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'HFE', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'HIN', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'HMC', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'IET', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'IEX', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'IND', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'IOT', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'IOX', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'KOZ', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'LLC', 'CSM', 'Y' );
INSERT INTO #test
VALUES ( 'MC1', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'MDE', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MDS', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MDX', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MPC', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'MWM', 'CSM', 'Y' );
INSERT INTO #test
VALUES ( 'N-A', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'NPM', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'NPP', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'NPR', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'NPS', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'QNC', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'QNS', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'SVB', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'SVM', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'SVS', 'CSM', 'M' );
INSERT INTO #test
VALUES ( 'SVV', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'TPA', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'WBS', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'WHM', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'ZTP', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'ZUM', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'ZZZ', 'CSM', NULL );
INSERT INTO #test
VALUES ( 'AGC', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'BET', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'CEX', 'ES', 'Y' );
INSERT INTO #test
VALUES ( 'CMA', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'CMN', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'CMO', 'ES', 'Y' );
INSERT INTO #test
VALUES ( 'COM', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'COX', 'ES', 'Y' );
INSERT INTO #test
VALUES ( 'FDI', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'FEH', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'FFB', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'HAM', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'HCO', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'HFE', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'HIN', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'HMC', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'IET', 'ES', 'Y' );
INSERT INTO #test
VALUES ( 'IEX', 'ES', 'Y' );
INSERT INTO #test
VALUES ( 'IND', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'IOT', 'ES', 'Y' );
INSERT INTO #test
VALUES ( 'IOX', 'ES', 'Y' );
INSERT INTO #test
VALUES ( 'KOZ', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'LLC', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MC1', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MDE', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MDS', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MDX', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MNS', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MPC', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MSP', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'MWM', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'N-A', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'NPM', 'ES', NULL );
INSERT INTO #test
VALUES ( 'NPP', 'ES', NULL );
INSERT INTO #test
VALUES ( 'NPR', 'ES', NULL );
INSERT INTO #test
VALUES ( 'NPS', 'ES', NULL );
INSERT INTO #test
VALUES ( 'QNC', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'QNS', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'SVB', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'SVM', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'SVS', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'SVV', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'TPA', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'WBS', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'WHM', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'ZTP', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'ZUM', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'ZZZ', 'ES', 'N' );
INSERT INTO #test
VALUES ( 'AGC', 'WP', NULL );
INSERT INTO #test
VALUES ( 'BET', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'CEX', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'CMA', 'WP', NULL );
INSERT INTO #test
VALUES ( 'CMN', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'CMO', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'COM', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'COX', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'FDI', 'WP', NULL );
INSERT INTO #test
VALUES ( 'FEH', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'FFB', 'WP', NULL );
INSERT INTO #test
VALUES ( 'HAM', 'WP', NULL );
INSERT INTO #test
VALUES ( 'HCO', 'WP', NULL );
INSERT INTO #test
VALUES ( 'HFE', 'WP', NULL );
INSERT INTO #test
VALUES ( 'HIN', 'WP', NULL );
INSERT INTO #test
VALUES ( 'HMC', 'WP', NULL );
INSERT INTO #test
VALUES ( 'IET', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'IEX', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'IND', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'IOT', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'IOX', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'KOZ', 'WP', NULL );
INSERT INTO #test
VALUES ( 'LLC', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'MC1', 'WP', NULL );
INSERT INTO #test
VALUES ( 'MDE', 'WP', NULL );
INSERT INTO #test
VALUES ( 'MDS', 'WP', NULL );
INSERT INTO #test
VALUES ( 'MDX', 'WP', NULL );
INSERT INTO #test
VALUES ( 'MNS', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'MNS', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'MNS', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'MNS', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'WP', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'WP', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'WP', NULL );
INSERT INTO #test
VALUES ( 'MNS-OLD', 'WP', NULL );
INSERT INTO #test
VALUES ( 'MPC', 'WP', NULL );
INSERT INTO #test
VALUES ( 'MSP', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'MSP', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'MSP', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'MSP', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'MWM', 'WP', 'Y' );
INSERT INTO #test
VALUES ( 'N-A', 'WP', NULL );
INSERT INTO #test
VALUES ( 'NPM', 'WP', NULL );
INSERT INTO #test
VALUES ( 'NPP', 'WP', NULL );
INSERT INTO #test
VALUES ( 'NPR', 'WP', NULL );
INSERT INTO #test
VALUES ( 'NPS', 'WP', NULL );
INSERT INTO #test
VALUES ( 'QNC', 'WP', NULL );
INSERT INTO #test
VALUES ( 'QNS', 'WP', NULL );
INSERT INTO #test
VALUES ( 'SVB', 'WP', NULL );
INSERT INTO #test
VALUES ( 'SVM', 'WP', NULL );
INSERT INTO #test
VALUES ( 'SVS', 'WP', NULL );
INSERT INTO #test
VALUES ( 'SVV', 'WP', NULL );
INSERT INTO #test
VALUES ( 'TPA', 'WP', NULL );
INSERT INTO #test
VALUES ( 'WBS', 'WP', NULL );
INSERT INTO #test
VALUES ( 'WHM', 'WP', NULL );
INSERT INTO #test
VALUES ( 'ZTP', 'WP', NULL );
INSERT INTO #test
VALUES ( 'ZUM', 'WP', NULL );
INSERT INTO #test
VALUES ( 'ZZZ', 'WP', NULL );
CREATE TABLE #FinalTest ( LOB VARCHAR(10), XLO VARCHAR(10), BPL VARCHAR(10), CAR VARCHAR(10), RA VARCHAR(10), ARA VARCHAR(10), PT VARCHAR(10), COCM VARCHAR(10), CBRAM VARCHAR(10),
EFI VARCHAR(10), DP VARCHAR(10), CVG VARCHAR(10), OP VARCHAR(10), HX VARCHAR(10), MHPD VARCHAR(10), SECTION VARCHAR(10), COBA VARCHAR(10), HMS VARCHAR(10),
BDM VARCHAR(10), MMM VARCHAR(10), ANM VARCHAR(10), CSM VARCHAR(10), ES VARCHAR(10), WP VARCHAR(10) );
INSERT INTO #FinalTest
VALUES ( 'COM', 'PS', NULL, 'HP', 'COM', 'COM', 'C', 'Y', 'Y', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', 'N', 'C', 'M', 'N',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'CMN', 'PS', NULL, 'HP', 'CMN', 'COM', 'C', 'Y', 'Y', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', 'N', 'C', 'M', 'N',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'FEH', 'PS', NULL, 'HP', 'COM', 'COM', 'C', 'N', 'N', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', 'N', 'F', 'M', 'N',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'SVS', 'SF', NULL, 'TP', 'SGH', 'SH', 'C', 'N', 'Y', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'S', NULL, NULL, 'M', 'N',
NULL );
INSERT INTO #FinalTest
VALUES ( 'TPA', 'TP', NULL, 'TP', 'OTH', 'TPA', 'C', 'N', 'N', 'X', '1', NULL,
'Y', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'AGC', 'TP', NULL, 'TP', 'OTH', 'TPA', 'C', 'N', 'N', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N',
NULL );
INSERT INTO #FinalTest
VALUES ( 'QNC', 'TP', NULL, 'TP', 'OTH', 'TPA', 'C', 'N', 'N', 'SVH', '1',
NULL, 'Y', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N',
NULL );
INSERT INTO #FinalTest
VALUES ( 'QNS', 'TP', NULL, 'TP', 'OTH', 'TPA', 'C', 'N', 'N', 'SVH', '1',
NULL, 'Y', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N',
NULL );
INSERT INTO #FinalTest
VALUES ( 'ZZZ', 'ZZ', NULL, 'XX', '---', NULL, 'X', 'N', 'N', 'X', '1', NULL,
'N', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'MSP', 'ML', 'MSEL%', 'HP', 'MSP', NULL, 'I', 'N', 'N', 'SVH', '4',
'111', 'N', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, 'M', NULL, 'N',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'MSP', 'MS', 'MSUP%', 'HP', 'MSP', NULL, 'I', 'N', 'N', 'SVH', '4',
'111', 'N', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, 'M', NULL, 'N',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'MNS-OLD', 'ML', 'MNSEL%', 'HP', 'MSP', NULL, 'I', 'N', 'N', 'X',
'4', '111', 'N', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'MNS-OLD', 'MS', 'MNSUP%', 'HP', 'MSP', NULL, 'I', 'N', 'N', 'X',
'4', '111', 'N', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'IND', 'IN', NULL, 'HP', 'IND', 'IND', 'I', 'N', 'N', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', 'N', 'C', 'M', 'N',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'SVV', 'SV', NULL, 'TP', 'OTH', NULL, 'C', 'N', 'N', 'X', '1', '0',
'N', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'HAM', 'RO', NULL, 'XX', 'OTH', NULL, 'C', 'N', 'N', 'SVH', '1',
'108', 'N', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N',
NULL );
INSERT INTO #FinalTest
VALUES ( 'KOZ', 'RO', NULL, 'XX', 'OTH', NULL, 'C', 'N', 'N', 'X', '1', NULL,
'N', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'MDE', 'RO', NULL, 'XX', 'OTH', NULL, 'C', 'N', 'N', 'X', '1', NULL,
'N', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'N-A', 'RO', NULL, 'XX', 'OTH', NULL, 'X', 'N', 'N', 'X', '1', NULL,
'N', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'FFB', 'TP', NULL, 'TP', 'OTH', 'TPA', 'C', 'N', 'N', 'SVH', '1',
NULL, 'Y', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N',
NULL );
INSERT INTO #FinalTest
VALUES ( 'WBS', 'RO', NULL, 'XX', 'OTH', NULL, 'C', 'N', 'N', 'X', '1', NULL,
'Y', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'SVM', 'SF', NULL, 'TP', 'SGH', 'SH', 'C', 'N', 'Y', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'S', NULL, NULL, 'M', 'N',
NULL );
INSERT INTO #FinalTest
VALUES ( 'WHM', 'TP', NULL, 'TP', 'OTH', 'TPA', 'C', 'N', 'N', 'SVH', '1',
NULL, 'Y', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N',
NULL );
INSERT INTO #FinalTest
VALUES ( 'ZUM', 'RP', NULL, 'XX', 'OTH', NULL, 'X', 'N', 'N', 'X', '1', '108',
'N', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'CMA', 'PS', NULL, 'HP', 'CMN', 'COM', 'I', 'N', 'Y', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'M', 'N',
NULL );
INSERT INTO #FinalTest
VALUES ( 'SVB', 'SF', NULL, 'TP', 'SGH', NULL, 'C', 'N', 'Y', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'S', NULL, NULL, 'M', 'N',
NULL );
INSERT INTO #FinalTest
VALUES ( 'MC1', 'SF', NULL, 'TP', 'SGH', NULL, 'C', 'N', 'Y', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'S', NULL, NULL, 'M', 'N',
NULL );
INSERT INTO #FinalTest
VALUES ( 'MNS', 'ML', 'MSEL%', 'HP', 'MSP', NULL, 'I', 'N', 'N', 'SVH', '4',
'111', 'N', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, 'M', NULL, 'N',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'MNS', 'MS', 'MSUP%', 'HP', 'MSP', NULL, 'I', 'N', 'N', 'SVH', '4',
'111', 'N', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, 'M', NULL, 'N',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'HFE', 'PS', NULL, 'HP', 'HOA', NULL, 'C', 'N', 'N', 'X', '2', '109',
'Y', 'Y', NULL, NULL, NULL, NULL, 'N', 'N', 'F', 'M', 'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'HMC', 'MM', NULL, 'HP', 'HOA', NULL, 'I', 'N', 'N', 'X', '2', '109',
'Y', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, 'M', NULL, 'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'HIN', 'IN', NULL, 'HP', 'HOA', NULL, 'C', 'N', 'N', 'X', '2', '109',
'Y', 'Y', NULL, NULL, NULL, NULL, 'N', 'N', 'H', 'M', 'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'HCO', 'PS', NULL, 'HP', 'HOA', NULL, 'C', 'N', 'N', 'X', '2', '109',
'Y', 'Y', NULL, NULL, NULL, NULL, 'N', 'N', 'H', 'M', 'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'IEX', 'EI', NULL, 'HP', 'IND', NULL, 'S', 'N', 'N', 'KZF', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', 'N', 'C', 'M', 'Y',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'CEX', 'EG', NULL, 'HP', 'COM', NULL, 'I', 'N', 'N', 'KZF', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', 'N', 'C', 'M', 'Y',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'IOX', 'EI', NULL, 'HP', 'IND', NULL, 'I', 'N', 'N', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', 'N', 'C', 'M', 'Y',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'COX', 'EG', NULL, 'HP', 'COM', NULL, 'I', 'N', 'Y', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', 'N', 'C', 'M', 'Y',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'CMO', 'EG', NULL, 'HP', 'CMN', NULL, 'I', 'N', 'Y', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', 'N', 'C', 'M', 'Y',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'MDX', 'MD', NULL, 'HP', 'MDX', NULL, 'X', 'N', 'N', 'LZC', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', NULL, 'N', NULL, 'N',
NULL );
INSERT INTO #FinalTest
VALUES ( 'MDS', 'MD', NULL, 'HP', 'MDS', NULL, 'X', 'N', 'N', 'LZC', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', NULL, 'N', NULL, 'N',
NULL );
INSERT INTO #FinalTest
VALUES ( 'IOT', 'EI', NULL, 'HP', 'IND', NULL, 'I', 'N', 'N', 'SVH', '3',
'112', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', 'N', 'C', NULL, 'Y',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'IET', 'EI', NULL, 'HP', 'IND', NULL, 'S', 'N', 'N', 'KZF', '3',
'112', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', 'N', 'C', NULL, 'Y',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'FDI', 'FD', NULL, 'HP', 'FDI', NULL, 'C', 'N', 'N', 'X', '1', NULL,
'N', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'MPC', 'MR', NULL, 'HP', 'MPC', NULL, 'I', 'N', 'N', 'X', '1', NULL,
'N', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'NPS', 'NP', NULL, 'NP', 'NPS', NULL, 'C', '?', 'N', 'NDP', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', NULL, NULL, NULL, NULL,
NULL );
INSERT INTO #FinalTest
VALUES ( 'NPP', 'NP', NULL, 'NP', 'NPP', NULL, 'C', '?', 'N', 'NDP', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', NULL, NULL, NULL, NULL,
NULL );
INSERT INTO #FinalTest
VALUES ( 'NPR', 'NP', NULL, 'NP', 'NPR', NULL, 'C', '?', 'N', 'NDP', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'N', NULL, NULL, NULL, NULL,
NULL );
INSERT INTO #FinalTest
VALUES ( 'NPM', 'NP', NULL, 'NP', 'NPM', NULL, 'C', 'N', 'N', 'X', '1', '108',
'Y', 'Y', NULL, NULL, NULL, NULL, 'N', NULL, NULL, NULL, NULL, NULL );
INSERT INTO #FinalTest
VALUES ( 'ZTP', 'DN', NULL, 'XX', 'DNR', NULL, 'X', 'N', 'N', 'X', '1', '108',
'N', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL );
INSERT INTO #FinalTest
VALUES ( 'BET', 'TP', NULL, 'TP', 'OTH', 'TPA', 'C', 'N', 'Y', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'Y', 'Y', 'Y', 'Y', 'N',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'LLC', 'TP', NULL, 'TP', 'OTH', 'TPA', 'C', 'N', 'Y', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'Y', 'Y', 'Y', 'Y', 'N',
'Y' );
INSERT INTO #FinalTest
VALUES ( 'MWM', 'TP', NULL, 'TP', 'OTH', 'TPA', 'C', 'N', 'Y', 'SVH', '1',
'108', 'Y', 'Y', NULL, NULL, NULL, NULL, 'Y', 'Y', 'Y', 'Y', 'N',
'Y' );
February 2, 2016 at 1:31 am
silverbullettruck (2/1/2016)
ChrisM/ER...Here is the full dataset i am working with and then below that is the what i need the data to look like after it is pivoted.
Sorry, I totally misread the problem. Let me get back to you.
Other than that you changed RBR to LOB, my solution should work fine. It's dynamically building a CROSS-TABS pivot, which will pivot the data no matter how many new attributes there are.
So for your new data-set: -
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = 'SELECT LOB, '
+ STUFF((SELECT ',' + CHAR(13) + CHAR(10)
+ 'MAX(CASE WHEN Attribute = ' + CHAR(39) + Attribute
+ CHAR(39) + ' THEN Value ELSE NULL END) AS ['
+ Attribute + ']'
FROM ( SELECT DISTINCT
Attribute
FROM #Test
) a
FOR XML PATH(''),
TYPE).value('.', 'NVARCHAR(MAX)'), 1, 3, '')
+ ' FROM #Test GROUP BY LOB;';
EXECUTE sp_executesql @SQL;
Returns: -
LOB XLO ANM DP OP SECTION MMM ARA COBA RA COCM WP CSM MHPD BPL HMS EFI HX CAR CBRAM CVG PT BDM ES
---------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
AGC TP NULL 1 Y NULL NULL TPA NULL OTH N NULL NULL NULL NULL NULL SVH Y TP N 108 C NULL N
BET TP Y 1 Y NULL Y TPA NULL OTH N Y Y NULL NULL NULL SVH Y TP Y 108 C Y N
CEX EG C 1 Y NULL N NULL NULL COM N Y M NULL NULL NULL KZF Y HP N 108 I N Y
CMA PS NULL 1 Y NULL NULL COM NULL CMN N NULL M NULL NULL NULL SVH Y HP Y 108 I NULL N
CMN PS C 1 Y NULL N COM NULL CMN Y Y M NULL NULL NULL SVH Y HP Y 108 C N N
CMO EG C 1 Y NULL N NULL NULL CMN N Y M NULL NULL NULL SVH Y HP Y 108 I N Y
COM PS C 1 Y NULL N COM NULL COM Y Y M NULL NULL NULL SVH Y HP Y 108 C N N
COX EG C 1 Y NULL N NULL NULL COM N Y M NULL NULL NULL SVH Y HP Y 108 I N Y
FDI FD NULL 1 N NULL NULL NULL NULL FDI N NULL NULL NULL NULL NULL X N HP N NULL C NULL N
FEH PS F 1 Y NULL N COM NULL COM N Y M NULL NULL NULL SVH Y HP N 108 C N N
FFB TP NULL 1 Y NULL NULL TPA NULL OTH N NULL NULL NULL NULL NULL SVH Y TP N NULL C NULL N
HAM RO NULL 1 N NULL NULL NULL NULL OTH N NULL NULL NULL NULL NULL SVH N XX N 108 C NULL N
HCO PS H 2 Y NULL N NULL NULL HOA N NULL M NULL NULL NULL X Y HP N 109 C N N
HFE PS F 2 Y NULL N NULL NULL HOA N NULL M NULL NULL NULL X Y HP N 109 C N N
HIN IN H 2 Y NULL N NULL NULL HOA N NULL M NULL NULL NULL X Y HP N 109 C N N
HMC MM M 2 Y NULL NULL NULL NULL HOA N NULL NULL NULL NULL NULL X Y HP N 109 I NULL N
IET EI C 3 Y NULL N NULL NULL IND N Y NULL NULL NULL NULL KZF Y HP N 112 S N Y
IEX EI C 1 Y NULL N NULL NULL IND N Y M NULL NULL NULL KZF Y HP N 108 S N Y
IND IN C 1 Y NULL N IND NULL IND N Y M NULL NULL NULL SVH Y HP N 108 I N N
IOT EI C 3 Y NULL N NULL NULL IND N Y NULL NULL NULL NULL SVH Y HP N 112 I N Y
IOX EI C 1 Y NULL N NULL NULL IND N Y M NULL NULL NULL SVH Y HP N 108 I N Y
KOZ RO NULL 1 N NULL NULL NULL NULL OTH N NULL NULL NULL NULL NULL X N XX N NULL C NULL N
LLC TP Y 1 Y NULL Y TPA NULL OTH N Y Y NULL NULL NULL SVH Y TP Y 108 C Y N
MC1 SF NULL 1 Y NULL NULL NULL NULL SGH N NULL M NULL NULL NULL SVH Y TP Y 108 C S N
MDE RO NULL 1 N NULL NULL NULL NULL OTH N NULL NULL NULL NULL NULL X N XX N NULL C NULL N
MDS MD N 1 Y NULL NULL NULL NULL MDS N NULL NULL NULL NULL NULL LZC Y HP N 108 X N N
MDX MD N 1 Y NULL NULL NULL NULL MDX N NULL NULL NULL NULL NULL LZC Y HP N 108 X N N
MNS MS M 4 N NULL NULL NULL NULL MSP N Y NULL NULL MSUP% NULL SVH Y HP N 111 I NULL N
MNS-OLD MS NULL 4 N NULL NULL NULL NULL MSP N NULL NULL NULL MNSUP% NULL X N HP N 111 I NULL N
MPC MR NULL 1 N NULL NULL NULL NULL MPC N NULL NULL NULL NULL NULL X N HP N NULL I NULL N
MSP MS M 4 N NULL NULL NULL NULL MSP N Y NULL NULL MSUP% NULL SVH Y HP N 111 I NULL N
MWM TP Y 1 Y NULL Y TPA NULL OTH N Y Y NULL NULL NULL SVH Y TP Y 108 C Y N
N-A RO NULL 1 N NULL NULL NULL NULL OTH N NULL NULL NULL NULL NULL X N XX N NULL X NULL N
NPM NP NULL 1 Y NULL NULL NULL NULL NPM N NULL NULL NULL NULL NULL X Y NP N 108 C N NULL
NPP NP NULL 1 Y NULL NULL NULL NULL NPP ? NULL NULL NULL NULL NULL NDP Y NP N 108 C N NULL
NPR NP NULL 1 Y NULL NULL NULL NULL NPR ? NULL NULL NULL NULL NULL NDP Y NP N 108 C N NULL
NPS NP NULL 1 Y NULL NULL NULL NULL NPS ? NULL NULL NULL NULL NULL NDP Y NP N 108 C N NULL
QNC TP NULL 1 Y NULL NULL TPA NULL OTH N NULL NULL NULL NULL NULL SVH N TP N NULL C NULL N
QNS TP NULL 1 Y NULL NULL TPA NULL OTH N NULL NULL NULL NULL NULL SVH N TP N NULL C NULL N
SVB SF NULL 1 Y NULL NULL NULL NULL SGH N NULL M NULL NULL NULL SVH Y TP Y 108 C S N
SVM SF NULL 1 Y NULL NULL SH NULL SGH N NULL M NULL NULL NULL SVH Y TP Y 108 C S N
SVS SF NULL 1 Y NULL NULL SH NULL SGH N NULL M NULL NULL NULL SVH Y TP Y 108 C S N
SVV SV NULL 1 N NULL NULL NULL NULL OTH N NULL NULL NULL NULL NULL X Y TP N 0 C NULL N
TPA TP NULL 1 Y NULL NULL TPA NULL OTH N NULL NULL NULL NULL NULL X Y TP N NULL C NULL N
WBS RO NULL 1 Y NULL NULL NULL NULL OTH N NULL NULL NULL NULL NULL X N XX N NULL C NULL N
WHM TP NULL 1 Y NULL NULL TPA NULL OTH N NULL NULL NULL NULL NULL SVH Y TP N NULL C NULL N
ZTP DN NULL 1 N NULL NULL NULL NULL DNR N NULL NULL NULL NULL NULL X Y XX N 108 X NULL N
ZUM RP NULL 1 N NULL NULL NULL NULL OTH N NULL NULL NULL NULL NULL X Y XX N 108 X NULL N
ZZZ ZZ NULL 1 N NULL NULL NULL NULL --- N NULL NULL NULL NULL NULL X N XX N NULL X NULL N
If you know the attributes and they're fixed, then you could do this instead: -
SELECT LOB,
MAX(CASE WHEN Attribute = 'XLO' THEN Value
ELSE NULL
END) AS [XLO],
MAX(CASE WHEN Attribute = 'ANM' THEN Value
ELSE NULL
END) AS [ANM],
MAX(CASE WHEN Attribute = 'DP' THEN Value
ELSE NULL
END) AS [DP],
MAX(CASE WHEN Attribute = 'OP' THEN Value
ELSE NULL
END) AS [OP],
MAX(CASE WHEN Attribute = 'SECTION' THEN Value
ELSE NULL
END) AS [SECTION],
MAX(CASE WHEN Attribute = 'MMM' THEN Value
ELSE NULL
END) AS [MMM],
MAX(CASE WHEN Attribute = 'ARA' THEN Value
ELSE NULL
END) AS [ARA],
MAX(CASE WHEN Attribute = 'COBA' THEN Value
ELSE NULL
END) AS [COBA],
MAX(CASE WHEN Attribute = 'RA' THEN Value
ELSE NULL
END) AS [RA],
MAX(CASE WHEN Attribute = 'COCM' THEN Value
ELSE NULL
END) AS [COCM],
MAX(CASE WHEN Attribute = 'WP' THEN Value
ELSE NULL
END) AS [WP],
MAX(CASE WHEN Attribute = 'CSM' THEN Value
ELSE NULL
END) AS [CSM],
MAX(CASE WHEN Attribute = 'MHPD' THEN Value
ELSE NULL
END) AS [MHPD],
MAX(CASE WHEN Attribute = 'BPL' THEN Value
ELSE NULL
END) AS [BPL],
MAX(CASE WHEN Attribute = 'HMS' THEN Value
ELSE NULL
END) AS [HMS],
MAX(CASE WHEN Attribute = 'EFI' THEN Value
ELSE NULL
END) AS [EFI],
MAX(CASE WHEN Attribute = 'HX' THEN Value
ELSE NULL
END) AS [HX],
MAX(CASE WHEN Attribute = 'CAR' THEN Value
ELSE NULL
END) AS [CAR],
MAX(CASE WHEN Attribute = 'CBRAM' THEN Value
ELSE NULL
END) AS [CBRAM],
MAX(CASE WHEN Attribute = 'CVG' THEN Value
ELSE NULL
END) AS [CVG],
MAX(CASE WHEN Attribute = 'PT' THEN Value
ELSE NULL
END) AS [PT],
MAX(CASE WHEN Attribute = 'BDM' THEN Value
ELSE NULL
END) AS [BDM],
MAX(CASE WHEN Attribute = 'ES' THEN Value
ELSE NULL
END) AS [ES]
FROM #Test
GROUP BY LOB;
That's essentially what the dynamic SQL is building.
February 2, 2016 at 2:13 am
Ahah! A bogstandard cross-tab. This article by Jeff [/url]explains how it works and how to extend the functionality:
SELECT
LOB,
[XLO] = MAX(CASE WHEN Attribute = 'XLO' THEN Value ELSE '' END),
[BPL] = MAX(CASE WHEN Attribute = 'BPL' THEN Value ELSE NULL END),
[CAR] = MAX(CASE WHEN Attribute = 'CAR' THEN Value ELSE '' END),
[RA] = MAX(CASE WHEN Attribute = 'RA' THEN Value ELSE '' END),
[ARA] = MAX(CASE WHEN Attribute = 'ARA' THEN Value ELSE NULL END),
[PT] = MAX(CASE WHEN Attribute = 'PT' THEN Value ELSE '' END),
[COCM] = MAX(CASE WHEN Attribute = 'COCM' THEN Value ELSE '' END),
[CBRAM] = MAX(CASE WHEN Attribute = 'CBRAM' THEN Value ELSE '' END),
[EFI] = MAX(CASE WHEN Attribute = 'EFI' THEN Value ELSE '' END),
[DP] = MAX(CASE WHEN Attribute = 'DP' THEN Value ELSE '' END),
[CVG] = MAX(CASE WHEN Attribute = 'CVG' THEN Value ELSE NULL END),
[OP] = MAX(CASE WHEN Attribute = 'OP' THEN Value ELSE '' END),
[HX] = MAX(CASE WHEN Attribute = 'HX' THEN Value ELSE '' END),
[MHPD] = MAX(CASE WHEN Attribute = 'MHPD' THEN Value ELSE NULL END),
[SECTION] = MAX(CASE WHEN Attribute = 'SECTION' THEN Value ELSE NULL END),
[COBA] = MAX(CASE WHEN Attribute = 'COBA' THEN Value ELSE NULL END),
[HMS] = MAX(CASE WHEN Attribute = 'HMS' THEN Value ELSE NULL END),
[BDM] = MAX(CASE WHEN Attribute = 'BDM' THEN Value ELSE NULL END),
[MMM] = MAX(CASE WHEN Attribute = 'MMM' THEN Value ELSE NULL END),
[ANM] = MAX(CASE WHEN Attribute = 'ANM' THEN Value ELSE NULL END),
[CSM] = MAX(CASE WHEN Attribute = 'CSM' THEN Value ELSE NULL END),
[ES] = MAX(CASE WHEN Attribute = 'ES' THEN Value ELSE NULL END),
[WP] = MAX(CASE WHEN Attribute = 'WP' THEN Value ELSE NULL END)
FROM #Test
GROUP BY LOB
ORDER BY LOB
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 2, 2016 at 2:26 am
Cadavre (2/2/2016)
silverbullettruck (2/1/2016)
ChrisM/ER...Here is the full dataset i am working with and then below that is the what i need the data to look like after it is pivoted.Sorry, I totally misread the problem. Let me get back to you.
...
I think you were correct first time, Craig 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 2, 2016 at 6:08 am
silverbullettruck (2/1/2016)
ChrisM/ER...Here is the full dataset i am working with and then below that is the what i need the data to look like after it is pivoted.
have you by chance duplicated some test data?
SELECT LOB, Attribute, Value
FROM #Test
WHERE (LOB like 'mns_old')
RETURNS 92 ROWS
I also see that you are expecting two rows in the pivot for 'mns_old' ....I assume that is correct?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 2, 2016 at 6:13 am
Yes, that entry was intentional as well as the other duplication in the results.
February 2, 2016 at 6:20 am
silverbullettruck (2/2/2016)
Yes, that entry was intentional as well as the other duplication in the results.
ok...so can I assume as well as duplicates that each LOB/Attribute pair can have 1 to N unique values?
also...please confirm that these are the only columns in your data....are there any columns that would provide some form of identifiers (ID/date etc?)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 51 total)
You must be logged in to reply to this topic. Login to reply