Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 528
  • Last Modified:

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
0
lrbrister
Asked:
lrbrister
1 Solution
 
nishant joshiTechnology Development ConsultantCommented:
you can do this with cte.
String concatination

please check above link..

Thanks
0
 
lrbristerAuthor Commented:
nishantcomp2512
Little more clarrification than just a link please?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lrbristerAuthor Commented:
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

0
 
nishant joshiTechnology Development ConsultantCommented:
Hi lrbrister,
sorry for the only link,below are the recursive cte which will solve your problem.
WITH t1(RID,mcount,email,link)
as 
(
SELECT cast(ROW_NUMBER() over(partition by email order by email) as int)RID,(count(link)) as mcount,cast(email as varchar(100)),cast('' as varchar(max)) as link
from tempdb.dbo.test1
group by email
UNION ALL
SELECT cast(t1.RID+1 as int) as RID,t1.mcount,cast(tab.email as varchar(100)),cast((t1.link+','+tab.link) as varchar(max)) as link
from t1
JOIN (SELECT ROW_NUMBER() over(partition by email order by email) RID,email,link from tempdb.dbo.test1) tab
ON t1.email=tab.email and t1.RID=tab.RID
)
SELECT email,link FROM t1
where mcount+1=RID
order by RK,RID

Open in new window


and below code for your test.

Drop table tempdb.dbo.test1 
SELECT 'lrbrister@mydomain.com' as email,'myLink1' as link INTO tempdb.dbo.test1 
UNION
SELECT 'lrbrister@mydomain.com' ,'myLink2'
UNION
SELECT 'lrbrister@mydomain.com','myLink3'
UNION
SELECT 'nishantcomp2512@gmail.com','newlink'

SELECT * from tempdb.dbo.test1

WITH t1(RID,mcount,email,link)
as 
(
SELECT cast(ROW_NUMBER() over(partition by email order by email) as int)RID,(count(link)) as mcount,cast(email as varchar(100)),cast('' as varchar(max)) as link
from tempdb.dbo.test1
group by email
UNION ALL
SELECT cast(t1.RID+1 as int) as RID,t1.mcount,cast(tab.email as varchar(100)),cast((t1.link+','+tab.link) as varchar(max)) as link
from t1
JOIN (SELECT RANK() over(order by email)as TRK,ROW_NUMBER() over(partition by email order by email) RID,email,link from tempdb.dbo.test1) tab
ON t1.email=tab.email and t1.RID=tab.RID --and t1.mcount>t1.RID
)
SELECT email,link FROM t1
where mcount+1=RID

Open in new window


Have a great day..
Thanks,
Nishant
0
 
keyuCommented:
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/
0
 
lrbristerAuthor Commented:
keyu
How on earth do I use that variable when there are 35000 repeating rows of different emails?
0
 
lrbristerAuthor Commented:
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.
0
 
lrbristerAuthor Commented:
Sorry for reply...I had a typo in my source query
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now