July 8, 2015 at 9:53 am
Hello All,
I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.
CH0, CH1, CH2, CHX Case
CM0, CM1, CM2, CMX Mechanical
I'm using T-SQL to generate the column type. Below is my DDL. Any help is appreciated.
Thanks.
USE tempdb
GO
CREATE TABLE ProdCodes
(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');
SELECT
p.Prefix
,p.Code
,'Type'=
case when SUBSTRING (p.code,2,1) = 'H' then 'Case'
when SUBSTRING (p.code,2,1) = 'M' then 'Mechanical'
when SUBSTRING (p.code,2,1) = 'N' then 'Not Bound'
when SUBSTRING (p.code,2,1) = ' ' then '?' End
FROM [ProdCodes] p (nolock)
where p.Prefix = 'b'
order by p.Code
July 8, 2015 at 10:55 am
Any help is greatly appreciated
July 8, 2015 at 11:27 am
SSRS Newbie (7/8/2015)
Any help is greatly appreciated
Try this on for size:
DECLARE @ProdCodes AS TABLE (
Prefix varchar(8),
Code varchar(5),
[Type] varchar(20)
);
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');
UPDATE P
SET P.[Type] =
CASE SUBSTRING (p.code, 2, 1)
WHEN 'H' THEN 'Case'
WHEN 'M' then 'Mechanical'
WHEN 'N' then 'Not Bound'
WHEN ' ' then '?'
END
FROM @ProdCodes AS P;
SELECT P.[Type],
STUFF(
(
SELECT ', ' + PC.Code
FROM @ProdCodes AS PC
WHERE PC.[Type] = P.[Type]
FOR XML PATH('')
), 1, 2, '') AS CODE_LIST
FROM @ProdCodes AS P
GROUP BY P.[Type]
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 8, 2015 at 11:37 am
Hi Steve,
The solution you suggested is exactly what I need. Do I have to add extra column type to my table? If so, I can't add the column to the table. Any suggestions? I appreciate your response
Thanks
July 8, 2015 at 11:50 am
Actually, you don't need it. Just create it on the fly as part of the query.
WITH CTE AS(
SELECT p.Prefix
,p.Code
,'Type'=
case when SUBSTRING (p.code,2,1) = 'H' then 'Case'
when SUBSTRING (p.code,2,1) = 'M' then 'Mechanical'
when SUBSTRING (p.code,2,1) = 'N' then 'Not Bound'
when SUBSTRING (p.code,2,1) = ' ' then '?' End
FROM [ProdCodes] p
where p.Prefix = 'b'
)
SELECT p.Prefix
,STUFF( (SELECT ', ' + RTRIM(i.Code)
FROM CTE i
WHERE p.Prefix = i.Prefix
AND p.Type = i.Type
FOR XML PATH('')), 1, 2, '')
,p.Type
FROM CTE p
GROUP BY p.Prefix, p.Type
Remember that you should never store this concatenated values in your database and you should use them only for presentation purposes.
July 8, 2015 at 11:52 am
i think it satisfy yours requirement
select d1.[type],'Code'= substring(
(select (', '+d.code) from
( SELECT
p.Prefix
,p.Code
,'Type'=
case when SUBSTRING (p.code,2,1) = 'H' then 'Case'
when SUBSTRING (p.code,2,1) = 'M' then 'Mechanical'
when SUBSTRING (p.code,2,1) = 'N' then 'Not Bound'
when SUBSTRING (p.code,2,1) = ' ' then '?' End
FROM [ProdCodes] p (nolock)
where p.Prefix = 'b'
) d where d.[Type]= d1.[type]
order by d.[Type] ,d.
for xml path( '')),3,1000)
from ( SELECT
p.Prefix
,p.Code
,'Type'=
case when SUBSTRING (p.code,2,1) = 'H' then 'Case'
when SUBSTRING (p.code,2,1) = 'M' then 'Mechanical'
when SUBSTRING (p.code,2,1) = 'N' then 'Not Bound'
when SUBSTRING (p.code,2,1) = ' ' then '?' End
FROM [ProdCodes] p (nolock)
where p.Prefix = 'b'
) d1
group by d1.[type]
July 8, 2015 at 12:02 pm
SSRS Newbie (7/8/2015)
Hi Steve,The solution you suggested is exactly what I need. Do I have to add extra column type to my table? If so, I can't add the column to the table. Any suggestions? I appreciate your response
Thanks
Here you go:
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];
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 8, 2015 at 1:31 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply