Hi,
I have a table with 10K emails in the below format:
Auto_ID email
1 email1@email.com
2 email2@email.com
3 email3@email.com
......
What I want is to get one row from the table above for each 600 emails out of the 10K emails. So this should return 17 rows as out emailing software only allows 600 recipients at the time in the below format:
email1@email.com; email2@email.com; email3@email.com ...................
Thank you.
Use a numbers table (or create one like shown below) and use this
CREATE TABLE #N(n INT NOT NULL PRIMARY KEY);
;WITH N(n) AS
( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 )
INSERT INTO #N
SELECT
ROW_NUMBER() OVER (ORDER BY a.n)-1
FROM
N a CROSS JOIN N b CROSS JOIN N c;
SELECT
a.n,
STUFF(b.emails,1,1,'') AS Emails
FROM
#N a
CROSS APPLY
(
SELECT ';' + email
FROM YourTable y
WHERE
Auto_Id > n*600
AND Auto_Id <= (n+1)*600
ORDER BY
Auto_Id
FOR XML PATH('')
) AS b(emails)
WHERE
n < 10000/600 + 1
DROP TABLE #N;Hi,
Thank you for your help, I've tried the query you've sent me and it's giving me an error, while the temporary table gets created:
CREATE TABLE #N(n INT NOT NULL PRIMARY KEY);
;WITH N(n) AS
( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 )
INSERT INTO #N
SELECT
ROW_NUMBER() OVER (ORDER BY a.n)-1
FROM
N a
CROSS JOIN N b
CROSS JOIN N c;
SELECT
a.n n,
STUFF(b.emails,1,1,'') AS Emails
FROM
#N a
CROSS APPLY
(
SELECT ';' + email as [emails]
FROM ADHOC.ALL_EMAIL y
WHERE
AUTO_NUMBER > n*600
AND AUTO_NUMBER <= (n+1)*600
ORDER BY
AUTO_NUMBER
FOR XML PATH('')
) AS b
WHERE
n < 10000/600 + 1
DROP TABLE #N;
Msg 8155, Level 16, State 2, Line 14
No column name was specified for column 1 of 'b'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'emails'.
Sorry about that. Fixed my previous post
....
) AS b(emails)
....Thanks, I'll modify the query tomorrow and will let you know.
Thanks again.
We use
FOR XML PATH(''), TYPE
-- NOTE: "'value" is case sensitive!!
).value('.', 'varchar(max)')
otherwise FOR XML will mangle any delimited characters - like < and >
O/P has a separator "; "i.e. including a space. I doubt that that is needed for a delimited list of email addresses, but if it is then:
...
STUFF(b.emails,1,2,'') AS Emails
...
SELECT '; ' + email
...
Indeed! This always should be done. But, somehow, I never do it, get into trouble at a later date, and then end up having to fix it!!
James,
After correcting the query it returned the desired results. However, it's adding some kind of HTML characters or functions in the below format:
emails>; email1@yahoo.com; email2@gmail.com; email3@hotmail.com; ..............................
******** for some reasons these characters don't show on my post but they are returned within the query result. I wish I could send you a screen shot but couldn't find the attachement option here.
is there a way to get rid off these expressions and just keep the string of emails with the (semi column) separator?
Thanks again for your help.
That is exactly the problem @Kristen was anticipating. Do what he suggested - see the code below
CREATE TABLE #N(n INT NOT NULL PRIMARY KEY);
;WITH N(n) AS
( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 )
INSERT INTO #N
SELECT
ROW_NUMBER() OVER (ORDER BY a.n)-1
FROM
N a CROSS JOIN N b CROSS JOIN N c;
SELECT
a.n,
STUFF(b.emails,1,1,'') AS Emails
FROM
#N a
CROSS APPLY
(
SELECT
(
SELECT ';' + email
FROM YourTable y
WHERE
Auto_Id > n*600
AND Auto_Id <= (n+1)*600
ORDER BY
Auto_Id
FOR XML PATH(''),TYPE
).value('.','varchar(max)')
) AS b(emails)
WHERE
n < 10000/600 + 1
DROP TABLE #N;
GOJamesK,
Thank you so much for your quick response on this one, the query finally worked and I even turned the count of total emails to be dynamic, below is the final query that I will be using moving forward:
DECLARE @CNT INT
SET @CNT=(SELECT COUNT(*) FROM YourTable)
CREATE TABLE #N(n INT NOT NULL PRIMARY KEY);
;WITH N(n) AS
( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 )
INSERT INTO #N
SELECT
ROW_NUMBER() OVER (ORDER BY a.n)-1
FROM
N a
CROSS JOIN N b
CROSS JOIN N c;
SELECT
a.n n,
STUFF(b.emails,1,1,'') AS Emails
FROM
#N a
CROSS APPLY
(
SELECT
(
SELECT '; ' + email as [emails]
FROM YourTable y
WHERE
AUTO_NUMBER > n*600
AND AUTO_NUMBER <= (n+1)*600
ORDER BY
AUTO_NUMBER
FOR XML PATH(''),TYPE
).value('.','varchar(max)'
)
) AS b (emails)
WHERE
n < @CNT/600 + 1
DROP TABLE #N;Glad it worked out. If you are going to have many more rows, the numbers table should have more entries as well. You can increase the number in the numbers table either by adding more rows in the cte or adding more cross joins as shown below.
You should have as many rows in the numbers table as you will have in the final output.
;WITH N(n) AS
( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
--- MORE ROWS IN THE CTE
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 )
INSERT INTO #N
SELECT
ROW_NUMBER() OVER (ORDER BY a.n)-1
FROM
N a
CROSS JOIN N b
CROSS JOIN N c
CROSS JOIN N d -- AND/OR MORE CROSS JOINSI just Cut and Paste ... solves that problem!
I have SNIPPETS files for all sorts of things, including StringFunctions and the like - which is basically just a list of all the "stock" SQL functions with any worked-examples I've built up over the years, and any Gotcha's I've learnt, so I can just Cut & Paste from that.
Actually working through the DOCs to create a worked example for each function brought some edge-conditions to light that I only discovered BECAUSE I read the DOCs carefully specifically for that job. Mind you, you lot probably know this stuff way better than I do in the first place ...
Its the useless crappy MARKDOWN that this site uses.
You have to put "" in front of all reserved characters to stop them being used as, in this case, HTML tags. For example
\<
will give you "<"
Of course 100% of people just type the reserved characters, some people here will know how to fix them, but even they have to re-read their posts to spot that the characters have got mangled ...
Kristen,
Yeah I know what you've meant, I had the same issue when I replied to James earlier. Thank you for the info though.