Best way to Iterate through temp table to build string in SQL Server 2014

Question

I have created a temp table, the idea being that I want to loop through it, match all records with the same email address and then populate a string, which will go in to an email, then drop the table. This will be run as a stored procedure.

I've used a cursor that first grabbed all the unique email addresses and then coalesce the records but with potentially 100k-500k records performance won't be acceptable, and I know there must be a far more efficient way of doing it.

Example data (apologies, don't know how to format it properly)

#temptable

temp_email, temp_string

[email protected] string1
[email protected] string2
[email protected] string3
[email protected] string4
[email protected] string5

I then want to populate another table with this data

emailto...   emailbody

[email protected]   'string1<br / > string2'
[email protected]   'string3<br / > string4'
[email protected]   'string5'

Thank you.

u can use String_agg() and this solution is also shared as a answer, do inform me if this doesn't serve your purpose. — Abubakar Riaz, Feb 20 '19 at 11:16
As has been commented a few times, `STRING_AGG` is SQL Server 2016+, @AbubakarRiaz — Thom A, Feb 20 '19 at 11:17

score 4 · Answer 1 · answered Feb 20 '19 at 11:13

4

The STUFF and FOR XML PATH method achieves this nicely in SQl Server 2014 and prior. Because you have the characters < and > however, they need to be "un-escaped" afterwards:

WITH VTE AS(
    SELECT *
    FROM (VALUES('[email protected]','string1'),
                ('[email protected]','string2'),
                ('[email protected]','string3'),
                ('[email protected]','string4'),
                ('[email protected]','string5')) V(Email, String))
SELECT Email,
       STUFF(REPLACE(REPLACE((SELECT '<br/>' + sq.String
                              FROM VTE sq
                              WHERE sq.Email = V.Email
                              FOR XML PATH('')),'&lt;','<'),'&gt;','>'),1,5,'')
FROM VTE V
GROUP BY Email;

answered Feb 20 '19 at 11:13

Thom A

88,727
11
45
75

Thank you, went for this solution. – user3442107 Feb 20 '19 at 11:59
2

Unescaping through string replacements is pretty horrible. The correct way would be `SELECT Email, STUFF((SELECT '
' + sq.String FROM VTE sq WHERE sq.Email = V.Email FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,5,'') FROM VTE V GROUP BY Email;` – Martin Smith Feb 20 '19 at 11:59
Thanks @MartinSmith, I'll need to remember that one. – Thom A Feb 20 '19 at 12:03

score 1 · Answer 2 · answered Feb 20 '19 at 11:11

1

you dont need to use cursor, please use string_agg function.

Create table #temptable 
(temp_email varchar(50), temp_string varchar(50))

INSERT INTO #temptable
VALUES ('[email protected]', 'string1'),
('[email protected]', 'string2'),
('[email protected]', 'string3'),
('[email protected]', 'string4'),
('[email protected]', 'string5')


Select temp_email, STRING_AGG(temp_string,' <br/>')
from #temptable
Group by temp_email

answered Feb 20 '19 at 11:11

Abubakar Riaz

320
8
26

1

Question specified SQL Server 2014, string_agg isn't available in that version – codeulike Feb 20 '19 at 11:15

score 0 · Answer 3 · answered Feb 20 '19 at 11:01

0

In SQL Server 2014 there are ways of doing this without a cursor but they are basically quite convoluted hacks and lead to pretty unreadable SQL in my opinion. See here for details:

How to concatenate text from multiple rows into a single text string in SQL server?

A cursor is arguably the best way in SQL 2014 because at least its readable.

In Sql Server 2017 there is an official aggregation function for this:

String_Agg

... but thats no use to you at the mo. Sorry.

answered Feb 20 '19 at 11:01

codeulike

22,514
29
120
167

Thank you, interesting link I'll take a look. The aggregation bit isn't the issue for me (as mentioned I'm using Coalesce to build the email body), its building the result set without a cursor if possible. – user3442107 Feb 20 '19 at 11:08
I'm not really sure `STUFF` and `FOR XML PATH` is really "convoluted" :) – Thom A Feb 20 '19 at 11:08
Opinions differ. I've been doing SQL for 20 years and would only use the XML PATH trick in production if it was surrounded by lots of comments describing what its doing and which character combinations it can't handle. – codeulike Feb 20 '19 at 11:18

score 0 · Answer 4 · edited Feb 20 '19 at 12:07

You can do something like this :

-- Create temp table
CREATE TABLE #temptable (temp_email varchar(50), temp_string varchar(50))

-- populate table with data
INSERT INTO #temptable
VALUES ('[email protected]', 'string1'),
('[email protected]', 'string2'),
('[email protected]', 'string3'),
('[email protected]', 'string4'),
('[email protected]', 'string5')

-- actual query
    ;WITH CTE_table AS(
     SELECT C.temp_email,
      REPLACE(REPLACE(STUFF(
        (SELECT  CAST(temp_string AS VARCHAR(20))+'<br/>' AS [text()]
         FROM #temptable AS O
         WHERE C.temp_email= o.temp_email
         FOR XML PATH('')), 1, 0, NULL)
         ,'&lt;','<') -- replace this < with html code &lt;
         ,'&gt;','>') -- replace this > with html code &gt;
         AS temp_string
         ,ROW_NUMBER() OVER (partition by temp_email order by temp_email) rownumber
    FROM #temptable AS C
    ) 
    -- Get only unique records
    SELECT temp_email,temp_string FROM CTE_table 
    Where rownumber=1

Best way to Iterate through temp table to build string in SQL Server 2014

4 Answers4