SQLTeam.com | Weblogs | Forums

Concatenate Data from multiple rows into one in increment of 600


#1

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.


#2

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;

#3

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;

Error:

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'.


#4

Sorry about that. Fixed my previous post

....
	) AS b(emails)
....

#5

Thanks, I'll modify the query tomorrow and will let you know.

Thanks again.


#6

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
...

#7

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!!


#8

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.


#9

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;

GO

#10

JamesK,

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;

#11

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 JOINS

#12

I 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 ...


#13

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 ...


#14

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.