asked on

SQL 2005 repeat?

I have a result set that I want to create a single line for each "group"

This is my return

email Link
lrbrister@mydomain.com myLink1
lrbrister@mydomain.com myLink2
lrbrister@mydomain.com myLink3

I want to collapse to this

email link
lrbrister@mydomain.com myLink1,myLink2,myLink3

WITHOUT doing a fetch if at all possible

nishant joshi

you can do this with cte.
String concatination

please check above link..

Thanks

Larry Brister

ASKER

nishantcomp2512
Little more clarrification than just a link please?

Aneesh

please check the similar post here

https://www.experts-exchange.com/questions/27837071/SQL-query-question-pivot-or-Cross-TAB.html

Larry Brister

ASKER

aneeshattingal

That works pretty well when I have just a few records.

You can see by the example code below it works very nicely

But when my source data has 35,000 rows, its another matter

This runs fine

DECLARE @t TABLE (email VARCHAR(30) NULL, link VARCHAR(300) NULL)
INSERT INTO @t
        ( email, link )
Select 'lbrister@mydomain.net', 'Link1' 
UNION ALL Select 'lbrister@mydomain.net', 'Link2' 
UNION ALL Select 'lbrister@mydomain.net', 'Link3' 
UNION ALL Select 'lbrister@mydomain.net', 'Link4' 
UNION ALL Select 'kmann@mydomain.net', 'Link1' 
UNION ALL Select 'kmann@mydomain.net', 'Link2' 
UNION ALL Select 'kmann@mydomain.net', 'Link3'


SELECT distinct email, SUBSTRING(
(SELECT ',' + s.link
FROM @t s
where s.email = p.email 
ORDER BY s.email
FOR XML PATH('')),2,200000) AS CSV
from @t p

Open in new window

If I do a simple select on my actual data it returns 35721 rows in one second
SELECT email,link FROM v_test_ClientEmailBlast

This takes 3 minutes + or more. Sometimes it just times out

SELECT distinct email, SUBSTRING(
(SELECT ',' + s.link
FROM v_test_ClientEmailBlast s
where s.email = p.email 
ORDER BY s.email
FOR XML PATH('')),2,200000) AS LINK
from v_test_ClientEmailBlast p

Open in new window

ASKER CERTIFIED SOLUTION

nishant joshi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

keyu

hi,

declare @mycatcode as varchar(max)

select @mycatcode=coalesce(@mycatcode,'')+link+',' from table_name where email='lrbrister@mydomain.com'

print @mycatcode

you can go for this link for more details

http://www.mssqltips.com/sqlservertip/1521/the-many-uses-of-coalesce-in-sql-server/

Larry Brister

ASKER

keyu
How on earth do I use that variable when there are 35000 repeating rows of different emails?

Larry Brister

ASKER

nishantcomp2512

That ran for 7 minutes. This can't be in a job...it has to be on demand.

At least, thats what the want and I'm trying my best to do it.

Larry Brister

ASKER

Sorry for reply...I had a typo in my source query