FOR XML question

Question

Post reply

FOR XML question

sqldriver

SSChampion

Points: 10440
More actions
September 10, 2013 at 10:47 am

#282334

Hi,
I use this code sometimes to illustrate which files duplicate records come from. It puts the email address in one column, and a comma separated list of IDs in another column. What I can't figure out is how to select ONLY records which have a comma in the ID column, so the non-duplicate records don't appear in the results. Whenever I try to do a WHERE col LIKE '%,%' I get an invalid column error, no matter how I seem to alias things. Do I need to put this query in a CTE or another SELECT to do that, or is there a way how it's set up now?
select distinct s2.email,
substring((select ', '+ cast(s1.id as varchar(64))
from dbo.j3688931 s1
where s1.email = s2.email
order by s1.id
for xml path ('')),2, 8000) [ids]
from dbo.j3688931 s2
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Have you tried using CHARINDEX?

WHERE CHARINDEX( ',', s1.id) > 0

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Another option would be using a different approach.

select s.email,

COUNT( distinct s.id) [ids]

from dbo.j3688931 s

GROUP BY s.email

HAVING COUNT( distinct s.id) > 1

I'm just guessing here because I can't see your data or expected results.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sqldriver SSChampion Points: 10440 More actions · Answer 3

Luis Cazares (9/10/2013)
Have you tried using CHARINDEX?
WHERE CHARINDEX( ',', s1.id) > 0

I haven't, since the issue was with the column name throwing an error (invalid column name or s1.col couldn't be found), not with LIKE not returning expected results.

sqldriver SSChampion Points: 10440 More actions · Answer 4

Luis Cazares (9/10/2013)
Another option would be using a different approach.
select s.email,
COUNT( distinct s.id) [ids]
from dbo.j3688931 s
GROUP BY s.email
HAVING COUNT( distinct s.id) > 1
I'm just guessing here because I can't see your data or expected results.

Here you go:

CREATE TABLE #dummy

(

ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

EMAIL NVARCHAR (64)

)

SET IDENTITY_INSERT #dummy ON

insert into #dummy (id, email)

SELECT '200001','jirq@hhufsgyirjtfqs.htr' UNION ALL

SELECT '200002','vijqjts@ttrry.htr' UNION ALL

SELECT '200003','gfsijrsts@ufhifihukg.htr' UNION ALL

SELECT '200004','gjrgrfs.frgujqqt@srurfitkfuuf.us' UNION ALL

SELECT '200005','jiuss@shhgru.htr' UNION ALL

SELECT '200006','ijggij.hjsijrsts@krtsts.htr' UNION ALL

SELECT '200007','ithtsstr@qis-wtti.trg' UNION ALL

SELECT '200008','titzktwitz@stihtruutjr.htr' UNION ALL

SELECT '200009','hfqgright@ithih.htr' UNION ALL

SELECT '200010','fisgfrgriffis@httrfiq.htr' UNION ALL

SELECT '200011','hkjftisg@gfrkfsht.htr' UNION ALL

SELECT '200012','ryfs@rqrjfqtyhtqiisgs.htr' UNION ALL

SELECT '200013','jruruhy@frjsgury.htr' UNION ALL

SELECT '200014','j.wfqsh.qfw@grfiq.htr' UNION ALL

SELECT '200015','rfry.qfgfrgj@gqtgfqsigs.htr' UNION ALL

SELECT '200016','hfsgiuqt@jqqitt-hs.trg' UNION ALL

SELECT '200017','gijfrfszt@rfsshtusisg.htr' UNION ALL

SELECT '200018','sfrjjrf@vigt.htr' UNION ALL

SELECT '200019','htqqjjs@gssjhurity.htr' UNION ALL

SELECT '200020','gtstjrgfri@ftrjsitjrjfqty.htr' UNION ALL

SELECT '200021','jrhrfsus@usfirst.trg' UNION ALL

SELECT '200024','fqfs.gfsijqq@fxitsiftf.htr' UNION ALL

SELECT '200026','frqjjs_ssyijr@sijrts.htr' UNION ALL

SELECT '200027','stjvj@fkrtstffihj.htr' UNION ALL

SELECT '200028','igjsjzrf@gfggjrifvjs.htr' UNION ALL

SELECT '200029','rksfug@s-g-g.htr' UNION ALL

SELECT '200030','wiiggqj@hfruttsgrjwjry.htr' UNION ALL

SELECT '200031','igjqhhjr@thgish.trg' UNION ALL

SELECT '200032','jthswfqr@yfhtt.htr' UNION ALL

SELECT '200033','rihhfjqh@4tfhtsih.htr' UNION ALL

SELECT '200034','hkjftisg@gfrkfsht.htr' UNION ALL

SELECT '200035','fsiir@qstftwfrj.htr' UNION ALL

SELECT '200036','shfsg@igjw103.htr' UNION ALL

SELECT '200037','fsthtsy.gqfkj@rgsif.ht.uk' UNION ALL

SELECT '200039','rkjssy@tjgh.htr' UNION ALL

SELECT '300001','ghfrt@iuffyshfsqjy.htr' UNION ALL

SELECT '300002','hfrtq.grjht@gjqksfuwhitj.htr' UNION ALL

SELECT '300003','kjri@gjsturfhtihfq.htr' UNION ALL

SELECT '300004','jqizfgjth.gfgsj@ussh.jiu' UNION ALL

SELECT '300005','uhyqqis.kfqgfhh@jjvs.trg' UNION ALL

SELECT '300006','tgrfus@htffrfs-ijvjqturjst.htr' UNION ALL

SELECT '300007','htqqjjs@gssjhurity.htr' UNION ALL

SELECT '300008','sjthfsssts@ufrtyhity.htr' UNION ALL

SELECT '300009','ijxtrwirjqjsswhtqjsfqj@yfhtt.htr' UNION ALL

SELECT '300010','srfsgrf@fgifisfshj.htr' UNION ALL

SELECT '300011','kfrfsifk@wjsqjyjshfshjiqivisg.trg' UNION ALL

SELECT '300012','krtgissts@gjsjvfgqtgfq.htr' UNION ALL

SELECT '300013','rk@usitji-uiuj.htr' UNION ALL

SELECT '300014','stjwfrt@qjrhfuitfq.htr' UNION ALL

SELECT '300015','qttvfr@rttiysftitsfq.htr' UNION ALL

SELECT '300016','gtstjrgfri@ftrjsitjrjfqty.htr' UNION ALL

SELECT '300017','wfhsht@ftq.htr' UNION ALL

SELECT '300018','rthhgjrt@gjjhhwttisj.htr' UNION ALL

SELECT '300019','kuurhjqq@jrjrstsjhtqtgihs.htr' UNION ALL

SELECT '300020','qrtrfs@rwftjr.htr' UNION ALL

SELECT '400001','ksigsgury@gfuqu.htr' UNION ALL

SELECT '400002','uwjqqs@gst.trg' UNION ALL

SELECT '400003','gjrtrfsirjsfui@gfqfxysjri.htr' UNION ALL

SELECT '400004','rqjvisj@hrrhuf.htr' UNION ALL

SELECT '400005','ghjfth@hishkqjyyfhhts.htr' UNION ALL

SELECT '400006','tshfsifqj123@httrfiq.htr' UNION ALL

SELECT '400007','gshjhu@trjvtr.trg' UNION ALL

SELECT '400008','fgjhkjr@fisfqfrr.htr' UNION ALL

SELECT '400009','rwr1@ftq.htr' UNION ALL

SELECT '400010','uhhifhhhijri@ujfgtiyurtujrtijs.htr' UNION ALL

SELECT '400011','ffhjfrr@fihtqj.htr' UNION ALL

SELECT '400012','grwtqhttt@ttws.wisihfr.rj.us' UNION ALL

SELECT '400013','kfrjs@thjwtsijryjfrs.isft' UNION ALL

SELECT '400014','juqif.rjqj@wsijvjqturjst.htr' UNION ALL

SELECT '400015','rrfhisttsh@hhhri.trg' UNION ALL

SELECT '400016','rjfqtyrfrt@sghgqtgfq.sjt' UNION ALL

SELECT '400017','fhtqqigfugh@htqjhfuitfq.htr' UNION ALL

SELECT '400018','kqrw@kw.htr' UNION ALL

SELECT '400019','uwjqqs@gst.trg' UNION ALL

SELECT '400020','trhuffjr@qiritjigrfsis.htr' UNION ALL

SELECT '400021','sfqvfttrj.itqhj@jjvs.trg' UNION ALL

SELECT '400022','ksrith@urirjhjfqthhfrj.htr' UNION ALL

SELECT '400023','rfyrtsi_gugijr_jr@grtws.jiu' UNION ALL

SELECT '400024','qfurf.jirjsjz@figjrwjg.htr' UNION ALL

SELECT '400025','hgrjwjr@htffrfs-ijvjqturjst.htr' UNION ALL

SELECT '400026','fkshitf.sfik@jqjhtrihissurfshj.htr' UNION ALL

SELECT '400027','sgriffis@gqfujr.htr' UNION ALL

SELECT '400028','rjjstryfisfshifq@yfhtt.htr' UNION ALL

SELECT '400029','srihhfjq@tzugrtuu.htr' UNION ALL

SELECT '400030','jrfrtis@hghtrrusihftitss.htr' UNION ALL

SELECT '400031','jtsfthfs@ffrrfsiwiqijrsjss.trg' UNION ALL

SELECT '400032','fhtqqigfugh@htqjhfuitfq.htr' UNION ALL

SELECT '400033','ftfrqj@sjt.rr.htr' UNION ALL

SELECT '500001','sfrf.qitts@giriitg.htr' UNION ALL

SELECT '500002','sfsirf@thjjxhigitsturhj.htr' UNION ALL

SELECT '500003','rufqrjr@girqshtutsjfstjrsrfss.trg' UNION ALL

SELECT '500004','srthhhjtti@frjrihfswisihfuitfq.htr' UNION ALL

SELECT '500005','frgjr@sysfuqs.sjt' UNION ALL

SELECT '500006','sshhiqqisg@gstsjtwtrks.htr' UNION ALL

SELECT '500007','tgfrsish@gitrttiv.htr' UNION ALL

SELECT '500008','ufuqg@ziqijifs.htr' UNION ALL

SELECT '500009','frfsif.rfyfijqi@htsshtqq.jiu' UNION ALL

SELECT '500010','skfqjiif@sigrf-rjsturhjs.htr' UNION ALL

SELECT '500011','gqufqqihh@siqvjrstfrrjfts.htr' UNION ALL

SELECT '500012','sfsitw@urthtssysist.htr' UNION ALL

SELECT '500013','jsgruswis@qftihrjtj.htr' UNION ALL

SELECT '500014','shturik.gtush@jts-hhf.htr' UNION ALL

SELECT '500015','uquigqjy@tfshf.htr' UNION ALL

SELECT '500016','fisgfrgriffis@httrfiq.htr' UNION ALL

SELECT '500017','kjvis@gighjfrtjigttks.htr' UNION ALL

SELECT '500018','hstfuqjs@ih-htru.htr' UNION ALL

SELECT '500019','s.ufrtykf@jttsr.sjt' UNION ALL

SELECT '500020','j.wfqsh.qfw@grfiq.htr' UNION ALL

SELECT '500021','hiuffy@rfshhhrytgjsihs.htr' UNION ALL

SELECT '500022','hhfgjfrty@ijujsifgqjhqjfsjrs.htr' UNION ALL

SELECT '500023','rruirf@tfshfuitfq.htr' UNION ALL

SELECT '500024','sifssgy@frfhfi.trg' UNION ALL

SELECT '500025','stjvj@fkrtstffihj.htr' UNION ALL

SELECT '500026','wyft@trhstqutitss.htr' UNION ALL

SELECT '500027','fqihjkrfvitsrj@ftq.htr' UNION ALL

SELECT '500028','sfsirf.shhqjsisgjr@ujriuj.htr' UNION ALL

SELECT '500029','gfiqjyfjihrus@rss.htr' UNION ALL

SELECT '500030','uujr@juiqsystjrs.htr' UNION ALL

SELECT '500031','itug.hyrfs@hfjthf.htr' UNION ALL

SELECT '500032','rfswyhkr@sfshufsh.gtv' UNION ALL

SELECT '500033','igjqhhjr@thgish.trg' UNION ALL

SELECT '500034','fqgjqrtstj@wjsqjyrjfqty.htr' UNION ALL

SELECT '500035','hrfig@rfrjjfrth.us' UNION ALL

SELECT '500036','igqfsjy@hhsht.trg' UNION ALL

SELECT '500037','vwfqqfhj@itrrf-usf.htr' UNION ALL

SELECT '500038','hrushisg@ghshuf.htr' UNION ALL

SELECT '500039','wiqq.gfgjq@trfijgj.htr' UNION ALL

SELECT '500040','rgfrgftt@hfriqttsqfsj.htr' UNION ALL

SELECT '500041','hrfhfir@sfxts-ufrtsjrs.htr' UNION ALL

SELECT '500042','rgsuisiqjr@hfrriststrfss.htr' UNION ALL

SELECT '500043','rfris@shfwrut.htr' UNION ALL

SELECT '500044','qiqyfst@grfiq.htr' UNION ALL

SELECT '500045','jjfssij@usitjitrfvjqgrtuu.htr' UNION ALL

SELECT '500046','jtsjuh.h.gutqjr@grfiq.htr' UNION ALL

SELECT '500047','rgfrgftt@hfriqttsqfsj.htr' UNION ALL

SELECT '500048','ifs.gqfhk@qfrisfttrsish.htr' UNION ALL

SELECT '500049','rqtrjshj@rj.htr' UNION ALL

SELECT '500050','wyft@trhstqutitss.htr' UNION ALL

SELECT '500051','sfssifjuqijs@ftt.sjt' UNION ALL

SELECT '600001','jtjh@hfssistrfrt.htr' UNION ALL

SELECT '600002','shjgqif@grfujvisjsjttqjrjst.htr' UNION ALL

SELECT '600003','gqjvy@jqqishqtwjs.htr' UNION ALL

SELECT '600004','rfyrtsi_gugijr_jr@grtws.jiu' UNION ALL

SELECT '600005','hkjftisg@gfrkfsht.htr' UNION ALL

SELECT '600006','rjrjiith.ifgjk@rgshitizjss.htr' UNION ALL

SELECT '600007','srfsgrf@fgifisfshj.htr' UNION ALL

SELECT '600008','frfs@guyrfqi.htr' UNION ALL

SELECT '600009','jhtury@grjftjrrjiif.htr' UNION ALL

SELECT '600010','hfsgiuqt@jqqitt-hs.trg' UNION ALL

SELECT '600011','uwiqqjtt@hfffsjrstiq.htr' UNION ALL

SELECT '600012','jhytisjs@sirtsisist.htr' UNION ALL

SELECT '600013','sgrtssts@tfrgjtjqjhtrihish.htr' UNION ALL

SELECT '600014','k.ufqvisfk@gfihtssuqtfsts.htr' UNION ALL

SELECT '600015','jrjrhijr@fjqqtwshiuhtusisg.trg' UNION ALL

SELECT '600016','fzugkt@ihfrjsujhifqists.htr' UNION ALL

SELECT '600017','giqqutvjsz@hfgjggjrryjrs.htr' UNION ALL

SELECT '600018','grtth@rtfrisgsurisg.htr' UNION ALL

SELECT '600019','stursjr@rhtijisqfsihtusisg.trg' UNION ALL

SELECT '600020','qrjifvii@tftitqj.htr' UNION ALL

SELECT '600021','gjtrgj.sihjwtsgjr@uisjt.htr' UNION ALL

SELECT '600022','rsfughtts@gfrqjttfht.htr' UNION ALL

SELECT '600023','qgjjgqj@hhfhtrufsijs.htr' UNION ALL

SELECT '600024','stjvj@fkrtstffihj.htr' UNION ALL

SELECT '600025','ituhhi@ftq.htr' UNION ALL

SELECT '600026','gtqzfhk@jxjtjr.jiu' UNION ALL

SELECT '600027','qshrtushirj@hiqqsiijshhttq.sjt' UNION ALL

SELECT '600028','uujr@juiqsystjrs.htr' UNION ALL

SELECT '600029','hkjftisg@gfrkfsht.htr' UNION ALL

SELECT '600030','r.grusjqqj@frtttjr.htr' UNION ALL

SELECT '600031','rikj.qjvisj@qisfrt.trg' UNION ALL

SELECT '600032','igjqhhjr@thgish.trg' UNION ALL

SELECT '600033','tjrry.utysjr@gfrhjsgrtuu.htr' UNION ALL

SELECT '600034','kjswhittisgtts@htrhfst.sjt' UNION ALL

SELECT '600035','ryfs@rqrjfqtyhtqiisgs.htr' UNION ALL

SELECT '600036','gqjss@hhjqts.htr' UNION ALL

SELECT '600037','kttrysf@urirfjxurjssish.htr' UNION ALL

SELECT '600038','iustjvjssts@giqgfsjht.htr' UNION ALL

SELECT '600039','htwfri@righjqq.ht.uk' UNION ALL

SELECT '600040','iqf@tfkrtstwftjr.htr' UNION ALL

SELECT '600041','kjs.sjwtts9@yfhtt.htr' UNION ALL

SELECT '600042','ifvii.h.tqsjs@wfrwihkri.htr' UNION ALL

SELECT '600043','hqfytts.hisjs@grjjshtrj.htr' UNION ALL

SELECT '600044','tjuii@hhhisft.trg' UNION ALL

SELECT '600045','ifvii@vfuqt-stqutitss.htr' UNION ALL

SELECT '600046','itssf.fjrrtsj@grttkfijqi.htr' UNION ALL

SELECT '600047','gsrith@gqtgfqujrstsfqs.ht.uk' UNION ALL

SELECT '600048','jiits@hfqqtwfyqfgs.htr' UNION ALL

SELECT '600049','ryfs@rqrjfqtyhtqiisgs.htr' UNION ALL

SELECT '600050','iuskisitsuts@fijrfsfgjrjst.htr' UNION ALL

SELECT '600051','ksigsgury@gfuqu.htr' UNION ALL

SELECT '600052','hjtyfq@sujfrjhtsuitfq.htr' UNION ALL

SELECT '700001','sstrjrs@gfrjsfhtru.htr' UNION ALL

SELECT '700002','ryfs@rqrjfqtyhtqiisgs.htr' UNION ALL

SELECT '700003','sifssgy@frfhfi.trg' UNION ALL

SELECT '700004','iqjgqfsh@hshtrj.htr' UNION ALL

SELECT '700005','srtsfhfs@guq.trg' UNION ALL

SELECT '700006','ifvii@ivjstufrtsjrs.htr' UNION ALL

SELECT '700007','urtjfurtrf@ftq.htr' UNION ALL

SELECT '700008','rgishtu@ijkfrjsjfrhh.htr' UNION ALL

SELECT '700009','ssjrffss@qititzrutufq.htr' UNION ALL

SELECT '700010','fssjttj.ktvfrjjs@gjfhtshs.htr' UNION ALL

SELECT '700011','jjssifjr.hfsh-wfij@fqqjghjsyhtusty.us' UNION ALL

SELECT '700012','rgtusfijqi@uihkfrihhiqtts.htr' UNION ALL

SELECT '700013','kirgy@jqrjrshhuqtz.htr' UNION ALL

SELECT '700014','fsthtsy.gqfkj@rgsif.ht.uk' UNION ALL

SELECT '700015','stjwfrt@qjrhfuitfq.htr' UNION ALL

SELECT '700016','rfrk.jihhhtrs@tutqttk.htr' UNION ALL

SELECT '700017','krjiqqy@ttws.frqisgtts.rf.us' UNION ALL

SELECT '700018','ffrftt@r-hjfqth.htr' UNION ALL

SELECT '700019','igjsjzrf@gfggjrifvjs.htr' UNION ALL

SELECT '700020','rfkjshrjiiy.th@grfiq.htr' UNION ALL

SELECT '700021','rtsfz@ijwtqfjrusih.ht.uk' UNION ALL

SELECT '700022','rfrty@gqfzjrhfu.htr' UNION ALL

SELECT '700023','jjssifjr@gqfzjrhfu.htr' UNION ALL

SELECT '700024','qtuis@ijqvfqrjssjsgjr.htr' UNION ALL

SELECT '700025','ufijqi@ujssutwjrgrtuu.htr' UNION ALL

SELECT '700026','rjsjj.gfuvis@rfgsfhtsuitfqity.htr' UNION ALL

SELECT '700027','fvfsjus@rjirjhqifjsfvjr.htr' UNION ALL

SELECT '700028','rqr@rftthjwrjrhjrqfw.htr' UNION ALL

SELECT '700029','hhris.griffis@uhtjsix-ish.htr' UNION ALL

SELECT '700030','hjtrifs140@rugisfsiyftjs.htr' UNION ALL

SELECT '700031','jqjstjr@hsjquity.htr' UNION ALL

SELECT '700032','tfruzzjtti@rfssfstit.rfss.jiu' UNION ALL

SELECT '700033','sihfi@sqhfrtfgj.htr' UNION ALL

SELECT '700034','rgfrgftt@hfriqttsqfsj.htr' UNION ALL

SELECT '700036','ktjfijs@uri.trg' UNION ALL

SELECT '700037','gtstjrgfri@ftrjsitjrjfqty.htr' UNION ALL

SELECT '700038','hhtsrfs@htriztsgjvjrfgj.htr' UNION ALL

SELECT '700039','fufivf@sgsss.htr' UNION ALL

SELECT '700040','ifwjgg@hrrhuf.htr' UNION ALL

SELECT '700041','ujtjrs@iiigrtuu.htr' UNION ALL

SELECT '800001','rwiqqjy@wiqijrht.htr' UNION ALL

SELECT '800002','fguttigijg@htitqjtsqisj.htr' UNION ALL

SELECT '800003','rwiqqjy@wiqijrht.htr' UNION ALL

SELECT '800004','rfrits.wjqqjs@isshtr.htr' UNION ALL

SELECT '800005','irfihhik@rirfsfgjrjst.htr' UNION ALL

SELECT '800006','gjth@gjrisifuiitvisufq.htr' UNION ALL

SELECT '800007','itssf.gruhfqj@qjftfijqiuihturjs.htr' UNION ALL

SELECT '800008','rjtsjs@isrttitssttrjs.htr' UNION ALL

SELECT '800009','rhisjs@gyqhtrufsijs.htr' UNION ALL

SELECT '800010','jkfiff@rjrfxqj.htr' UNION ALL

SELECT '800011','jikfrkhjhk@sujfrjhtsuitfq.htr' UNION ALL

SELECT '800012','hfrqfsiqvf@urirj-hqjfsisg.htr' UNION ALL

SELECT '800013','ggqthk@frqhfu.htr' UNION ALL

SELECT '800014','ghtqfstutsi@rrhgrtfihfst.htr' UNION ALL

SELECT '800015','igqfsjy@hhsht.trg' UNION ALL

SELECT '800016','jths.gjqhhjrs@fsuiijrstqutitss.htr' UNION ALL

SELECT '800017','qfzfq.rtttrs@grfiq.htr' UNION ALL

SELECT '800018','rfqqrji@gqissfqqrji.htr' UNION ALL

SELECT '800019','frtrjrt@guhkjshjirjr.htr' UNION ALL

SELECT '800020','kthtrfs@trujstrthhttjqs.htr' UNION ALL

SELECT '800021','shjgjrt@ijrry.k12.sh.us' UNION ALL

SELECT '800022','uhiq.ufysj@gtrjj.htr' UNION ALL

SELECT '800023','rsfqfs@qufi1.htr' UNION ALL

SELECT '800024','rurftt@fffjh.htr' UNION ALL

SELECT '800025','rftt.krusj@rjgishtru.htr' UNION ALL

SELECT '800026','fiisirtsj@vsisfqjs.htr' UNION ALL

SELECT '800027','qisif.hrjwj@rwv.htr' UNION ALL

SELECT '800028','sfrf.qitts@giriitg.htr' UNION ALL

SELECT '800029','sqfyttsstuiit@yfhtt.htr' UNION ALL

SELECT '800030','frhivjr@uih.trg' UNION ALL

SELECT '800031','irfihhik@rirfsfgjrjst.htr' UNION ALL

SELECT '800032','sihhtqfs.ijrf@uhiqf.gtv' UNION ALL

SELECT '800033','sgfqvis@gtsttsistjrfhtivj.htr' UNION ALL

SELECT '800034','isft@gtsttshtruutjrurts.htr' UNION ALL

SELECT '800035','jtshthtsst@yfhtt.htr' UNION ALL

SELECT '800036','grfhjqirf@qjfiisgjigjfgjsts.htr' UNION ALL

SELECT '800037','jrhrfsus@usfirst.trg' UNION ALL

SELECT '900001','kfsgfrttuqurgisg@ftq.htr' UNION ALL

SELECT '900002','rgrfsht@uhifgrtuu.htr' UNION ALL

SELECT '900003','jtfssj@jjigfrhuf.htr' UNION ALL

SELECT '900004','tfruzzjtti@rfssfstit.rfss.jiu' UNION ALL

SELECT '900005','jthss@hujquity.htr' UNION ALL

SELECT '900006','ksigsgury@gfuqu.htr' UNION ALL

SELECT '900007','rihh@tisrtsjy.htr' UNION ALL

SELECT '900008','tyhrjftits@hqjfr.sjt' UNION ALL

SELECT '900009','fqjxfsijrfisjufistisg@grfiq.htr' UNION ALL

SELECT '900010','rgtifvj@htrhfst.sjt' UNION ALL

SELECT '900011','uiirfshit@stfrfshishtusj.trg' UNION ALL

SELECT '900012','guiurfsi@tjqjrftihgqtgfq.htr' UNION ALL

SELECT '900013','ji@hytjhhrushrttrs.htr' UNION ALL

SELECT '900014','rfrjs.rtss@thtrsts.htr' UNION ALL

SELECT '900015','fisfshj@jqjuhfstffriqy.trg' UNION ALL

SELECT '900016','fhgtyjr@rfhtqiisgsqqh.htr' UNION ALL

SELECT '900017','riqt.hf@htrhfst.sjt' UNION ALL

SELECT '900018','frifvjtrv@grfiq.htr' UNION ALL

SELECT '900019','qjsqij_ify@htshtrifhfijry.trg' UNION ALL

SELECT '900020','kfrjs@thjwtsijryjfrs.isft' UNION ALL

SELECT '900021','hfqyss@wisgftjhtrufsijs.htr' UNION ALL

SELECT '900022','sfrfh@utwjrskirs.htr' UNION ALL

SELECT '900023','iwiqqsts@swhtstrtqs.sjt' UNION ALL

SELECT '900024','ujtjr.trqfsit@gfk.htr' UNION ALL

SELECT '900025','jsgruswis@qftihrjtj.htr' UNION ALL

SELECT '900026','ghqisjfjqtjr@istjrtjhhsjhurity.htr' UNION ALL

SELECT '900027','sfqjs@hrrfsuffhturisg.htr' UNION ALL

SELECT '900028','uftrihk.qfwrjshj@qjtsfgrtuu.htr' UNION ALL

SELECT '900029','jguhhi@rjitrr.htr' UNION ALL

SELECT '900030','thjrjsfshfvtsj@istv-8.htr' UNION ALL

SELECT '900031','rgtwrfs@gfgsts.jiu' UNION ALL

SELECT '900032','uwiqqjtt@hfffsjrstiq.htr' UNION ALL

SELECT '900033','krfzzjt@yrhfgtstts.trg' UNION ALL

SELECT '900034','jruirjfssjtrjhtvjry@ftq.htr' UNION ALL

SELECT '900035','sfqjs@tvttyz.htr' UNION ALL

SELECT '900036','hhfrqij@grfsijstqutitssstfffisg.htr' UNION ALL

SELECT '900037','ghtqqiss@jquiurjstrfrkjtjrs.sjt' UNION ALL

SELECT '900038','gtqijssifr@sghgqtgfq.sjt' UNION ALL

SELECT '900039','rgttthgy@4fusiisg.htr' UNION ALL

SELECT '900040','jitrj@gitstrjfr.htr' UNION ALL

SELECT '900041','yrf168@yfhtt.htr' UNION ALL

SELECT '900042','gfshfijg@ftq.htr' UNION ALL

SELECT '900043','hkjftisg@gfrkfsht.htr' UNION ALL

SELECT '900044','ktjsqjj2011@grfiq.htr'

SET IDENTITY_INSERT #dummy OFF

select distinct s2.email,

substring((select ', '+ cast(s1.id as varchar(64))

from #dummy s1

where s1.email = s2.email

order by s1.id

for xml path ('')),2, 8000) [ids]

from #dummy s2

where s2.email is not null

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

If I understand correctly, you want to show the emails that have multiple ids.

Here are 2 options. If I continue to be wrong, please explain what's the problem 🙂

WITH CTE AS(

select distinct s2.email,

substring((select ', '+ cast(s1.id as varchar(64))

from #dummy s1

where s1.email = s2.email

order by s1.id

for xml path ('')),2, 8000) [ids]

from #dummy s2

where s2.email is not null)

SELECT *

FROM CTE

WHERE ids LIKE '%,%';

select s2.email,

substring((select ', '+ cast(s1.id as varchar(64))

from #dummy s1

where s1.email = s2.email

order by s1.id

for xml path ('')),2, 8000) [ids]

from #dummy s2

where s2.email is not null

GROUP BY s2.EMAIL

HAVING COUNT( DISTINCT s2.id) > 1

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sqldriver SSChampion Points: 10440 More actions · Answer 6

Luis Cazares (9/10/2013)
If I understand correctly, you want to show the emails that have multiple ids.
Here are 2 options. If I continue to be wrong, please explain what's the problem 🙂
WITH CTE AS(
select distinct s2.email,
substring((select ', '+ cast(s1.id as varchar(64))
from #dummy s1
where s1.email = s2.email
order by s1.id
for xml path ('')),2, 8000) [ids]
from #dummy s2
where s2.email is not null)
SELECT *
FROM CTE
WHERE ids LIKE '%,%';
select s2.email,
substring((select ', '+ cast(s1.id as varchar(64))
from #dummy s1
where s1.email = s2.email
order by s1.id
for xml path ('')),2, 8000) [ids]
from #dummy s2
where s2.email is not null
GROUP BY s2.EMAIL
HAVING COUNT( DISTINCT s2.id) > 1

Nope, those both look right. I think I get it. That's a nifty trick with the distinct count. It probably would have occurred to me on my death bed.

Distiiiiinct...

What? What stinks?

Diiiiiistiiiiinct...