select c.customerid,(c.lastname+', '+c.firstname) customername, c.companyname, d.companyname+' ('+d.lastname+','+d.firstname+')' "parent companyname(last, first)",s.siteid, s.nickname sitename, dbo.getsitetelemetryboxlist(s.siteid) "deviceid's", dbo.getsitetelemetryboxskulist(s.siteid,0) sku site s inner join customer c on s.customerid = c.customerid inner join customer d on d.customerid = c.parentcustomerid s.createdate between dateadd(day, -65, getutcdate()) , getutcdate() order c.customerid, s.siteid the above query returns values this:
cid customername companyname parent companyname(last, first) sitename deviceid sku 888296 deyoung, scott deyoung farms mercier valley irrigation (mercier,ralph) h e east 200241 netb12wr 890980 rust, marcus null chester inc. (young,scott) byroad east 346370 netb12wr 890980 rust, marcus null chester inc. (young,scott) byroad west 345431 netb12wr 891094 pirani, mark pirani farm amx irrigation (burroughs,michael) hwy 64 south 333721 unknown 891094 pirani, mark pirani farm amx irrigation (burroughs,michael) hwy 64 north 250162 netb12wr 891094 pirani, mark pirani farm amx irrigation (burroughs,michael) hwy 64 west 250164 netb12wr 891094 pirani, mark pirani farm amx irrigation (burroughs,michael) hwy 64 east 250157 netb12wr 891430 gammil, bob gammil farms amx irrigation (burroughs,michael) angel 333677 unknown 891430 gammil, bob gammil farms amx irrigation (burroughs,michael) cemetery 333564 unknown the problem face if customerid/name repeating in result set. sitename, deviceid, sku should concatenated represent data 1 value. example, mark pirani row like
cid customername ... sitename deviceid's ... 891904 pirani, mark ... hwy 64 south, hwy 64 north, hwy 64 west, hwy 64 east 333721,250162,250164,250157 ...
you can convert rows transform rows concatenated string:
select distinct stuff(( select ',' + u.username users u u.username = username order u.username xml path('') ),1,1,'') userlist users group username 
Comments
Post a Comment