December 27, 2013 at 7:28 am
Please see below cursor which produces my required results. It returns one row per customer with all their emails concatenated onto one column. I have tried various options including experimenting with PIVOT but have been unsuccessful
Assuming an unknown number of customers and an unknown number of emails per customer, can anyone help to remove the cursor and simplify this TSQL please?
create table emails (id int identity(1,1), customer_id int, email_text varchar(max))
insert into emails (customer_id, email_text) values (22, 'some text 22a')
insert into emails (customer_id, email_text) values (22, 'some text 22b')
insert into emails (customer_id, email_text) values (22, 'some text 22c')
insert into emails (customer_id, email_text) values (22, 'some text 22d')
insert into emails (customer_id, email_text) values (23, 'some text 23a')
insert into emails (customer_id, email_text) values (23, 'some text 23b')
insert into emails (customer_id, email_text) values (23, 'some text 23c')
insert into emails (customer_id, email_text) values (24, 'some text 24a')
DECLARE @cur_id int
DECLARE @customer_id int
DECLARE@emails varchar(max)
DECLARE cur cursor for select customer_id, email_text from emails
open cur
fetch next from cur into @customer_id, @emails
WHILE @@FETCH_STATUS = 0
begin
SET @cur_id = @customer_id
DECLARE @sql_mail VARCHAR(max)
SELECT @sql_mail = COALESCE(@sql_mail,'') + @emails + ' -- '
fetch next from cur into @customer_id, @emails
If @cur_id <> @customer_id
begin
select @cur_id, @sql_mail
SELECT @sql_mail = ''
end
end
select @customer_id, @sql_mail
close cur
deallocate cur
drop table emails
December 27, 2013 at 8:05 am
What you're trying to do is explained on this article. http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Read it and check the solution.
SELECT customer_id,
all_emails = STUFF((SELECT ' -- ' + email_text
FROM emails x
WHERE x.customer_id = e.customer_id
FOR XML PATH('')), 1, 4, '')
FROM emails e
GROUP BY customer_id
If you have any questions about how it works, feel free to ask.
December 27, 2013 at 8:10 am
Luis
Thats exactly what I want, thank you.
Allan
December 27, 2013 at 8:16 am
You're welcome.
But, do you understand how it works?
December 27, 2013 at 10:30 am
Hi Luis
Yes I now understand this thank you.
Allan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply