April 7, 2015 at 9:34 am
Hi All, I have a need to create a delimited string so that I can use this to create a data driven subscription on SSRS, I am pretty sure I am making a fairly simple task a lot harder than it needs to be here.
In the below code, I need to create a delimited string using the branch number, grouped by the email address
USE tempdb
GO
IF OBJECT_ID('tempdb..#emails') IS NOT NULL
BEGIN
DROP TABLE #emails
END
CREATE TABLE #emails
(
Email VARCHAR(50) ,
BranchNumber VARCHAR(5)
)
INSERT INTO #emails
VALUES
( 'Branch101@ACA.com', 'A54' ),
( 'Branch101@ACA.com', 'A56' ),
( 'Branch101@ACA.com', 'A59' ),
( 'Branch102@ACA.com', 'B49' ),
( 'Branch102@ACA.com', 'B54' ),
( 'Branch102@ACA.com', 'B10' ),
( 'Branch103@ACA.com', 'C80' )
The output I am looking for is something along the lines of...
Email, BranchNumber
----------------------------------------------------------
Branch101@ACA.com A54,A56,A59
Branch102@ACA.com B49,B54,B10
Thanks
Jim
April 7, 2015 at 9:45 am
great job with the same data!
here's one way to do it, featuring FOR XML to do the concatenation:
/*--Results
Email(No column name)
Branch101@ACA.comA54, A56, A59
Branch102@ACA.comB49, B54, B10
*/
SELECT Email
,STUFF(
(
SELECT ', ' + B.BranchNumber
FROM #emails B
WHERE A.Email = B.Email
FOR XML PATH(''))
,
1, 2, '')
FROM #emails A
GROUP BY Email
Lowell
April 7, 2015 at 9:46 am
Using the technique outlined in Creating a comma-separated list (SQL Spackle)[/url] By Wayne Sheffield you could do this:
WITH de AS
(
SELECT DISTINCT Email
FROM #emails
)
SELECT
Email,
BranchNumber = STUFF((
SELECT ',' + BranchNumber
FROM #emails e
WHERE e.email = de.Email
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
FROM de;
-- Itzik Ben-Gan 2001
April 7, 2015 at 9:50 am
Alan, Lowell
Thank you both for your answers, they both work and give me exactly what I am looking for.
Appreciate the help.
Jim
April 7, 2015 at 5:52 pm
Jim-S (4/7/2015)
Alan, LowellThank you both for your answers, they both work and give me exactly what I am looking for.
Appreciate the help.
Jim
Are you aware of what the difference in the two queries is intended to do?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 8, 2015 at 1:30 am
Hi Dwain,
No, I'm not aware. I assumed it was pretty much two slightly different methods to achieve the same result.
April 8, 2015 at 1:48 am
Jim-S (4/8/2015)
Hi Dwain,No, I'm not aware. I assumed it was pretty much two slightly different methods to achieve the same result.
Alan has given you the more general form that will always work but is also slightly slower. There are some special characters that, when appearing in the string will cause the concatenated result to be off. These are found here: http://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references in the section on Predefined entities in XML.
Try this to see the difference in results:
CREATE TABLE #emails
(
Email VARCHAR(50) ,
BranchNumber VARCHAR(5)
)
INSERT INTO #emails
VALUES
( 'Branch101@ACA.com', 'A54' ),
( 'Branch101@ACA.com', 'A56' ),
( 'Branch101@ACA.com', 'A>9' ),
( 'Branch102@ACA.com', 'B<9' ),
( 'Branch102@ACA.com', 'B''4' ),
( 'Branch102@ACA.com', 'B&0' ),
( 'Branch103@ACA.com', 'C"0' );
-- Lowell's solution
SELECT Email
,STUFF(
(
SELECT ', ' + B.BranchNumber
FROM #emails B
WHERE A.Email = B.Email
FOR XML PATH(''))
,
1, 2, '')
FROM #emails A
GROUP BY Email;
-- Alan.B's solution
WITH de AS
(
SELECT DISTINCT Email
FROM #emails
)
SELECT
Email,
BranchNumber = STUFF((
SELECT ',' + BranchNumber
FROM #emails e
WHERE e.email = de.Email
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
FROM de;
GO
DROP TABLE #emails;
Using TYPE/.value as Alan has done eliminates this parsing issue.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 8, 2015 at 2:00 am
dwain.c (4/8/2015)
Jim-S (4/8/2015)
Hi Dwain,No, I'm not aware. I assumed it was pretty much two slightly different methods to achieve the same result.
Alan has given you the more general form that will always work but is also slightly slower. There are some special characters that, when appearing in the string will cause the concatenated result to be off. These are found here: http://en.wikipedia.org/wiki/List_of_XML_and_HTML_character_entity_references in the section on Predefined entities in XML.
Using TYPE/.value as Alan has done eliminates this parsing issue.
Yes I can see the difference in the results, thanks for taking the time to explain. The branch number I am using will always be 2 letters followed by a number.
Thanks again.
Jim
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply