sgmunson (7/8/2015)
DECLARE @ProdCodes AS TABLE (
Prefix varchar(8),
Code varchar(5)
);
INSERT INTO @ProdCodes (Prefix, Code)
VALUES
('b', 'CH0'),
('b', 'CH1'),
('b', 'CH2'),
('b', 'CHX'),
('b', 'CM0'),
('b', 'CM1'),
('b', 'CM2'),
('b', 'CMX'),
('b', 'CN0'),
('b', 'CN1'),
('b', 'CN2'),
('b', 'CMX');
WITH CTE AS (
SELECT Prefix, Code,
CASE SUBSTRING (code, 2, 1)
WHEN 'H' THEN 'Case'
WHEN 'M' then 'Mechanical'
WHEN 'N' then 'Not Bound'
WHEN ' ' then '?'
END AS [Type]
FROM @ProdCodes
)
SELECT P.[Type],
STUFF(
(
SELECT ', ' + PC.Code
FROM CTE AS PC
WHERE PC.[Type] = P.[Type]
FOR XML PATH('')
), 1, 2, '') AS CODE_LIST
FROM CTE AS P
GROUP BY P.[Type];
Unfortunately, this code requires two table scans. I came up with a similar approach that only requires one table scan. This could make a big difference if your table is relatively large.
DECLARE @ProdCodes TABLE(
Prefix char(8),
Code char(5)
);
INSERT INTO @Prodcodes (Prefix, Code)
VALUES ('b', 'CH0'),
('b', 'CH1'),
('b', 'CH2'),
('b', 'CHX'),
('b', 'CM0'),
('b', 'CM1'),
('b', 'CM2'),
('b', 'CMX'),
('b', 'CN0'),
('b', 'CN1'),
('b', 'CN2'),
('b', 'CMX'),
('b', 'CZY');
SELECT STUFF(pc.code_list, 1, 2, '') AS code_list, c.category
FROM (
VALUES('b', '_H%', 'Case'),
('b', '_M%', 'Mechanical'),
('b', '_N%', 'Not Bound'),
('b', '_[^HMN]%', '?')
) AS c(prefix, pattern, category)
CROSS APPLY (
SELECT
', ' + p.Code
FROM @ProdCodes p
WHERE p.Prefix = c.prefix
AND p.Code LIKE c.pattern
FOR XML PATH('')
) AS pc(code_list)
Also, if you can change the pattern so that the first character is not a wildcard, it will be able to use the patterns in index seeks. Given the data you've supplied, the first pattern could be 'CH%', for example. The other issue with this approach is that it will exclude any records that don't match one of the specified patterns.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA