Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Tampapat
Starting Member
18 Posts |
 Posted - 2012-02-22 : 15:23:32
|
| I have a field in the Database Entitled CXXREFCOD this can contain numerous entries into the same field. for instance it may contain for one person XXXX1, XXXX2 and XXXX3 (with XXXX being a field name) the field is the same however the results can be multiple for the same field. I need to Coalesce the fields into giving me all of them and not just the first one. CAN YOU PLEASE HELP?Patrick Palmer |
|
|
Tampapat
Starting Member
18 Posts |
Posted - 2012-02-22 : 15:24:30
|
| Sorry here is the code:SELECT distinct po.poid, polastname as LastName, pofirstnam as FirstName, pomiddle as MiddleName, porspartic as RSActive, convert(varchar,po20,101) as Lastappdate, OFADDRESS1 as Address, OFADDRESS2 as Address2, ofcity as City, ofstate as State, ofzip5 as Zip, coalesce(cxxrefcod, 0), CXXREFCOD as CrossReference, Patrick Palmer |
 |
|
|
Tampapat
Starting Member
18 Posts |
Posted - 2012-02-22 : 15:24:49
|
| CXXREFCOD as CrossReference, hauser2, porssdate, hs.hsid + hsname as facility, hastatus, hanoref, (SELECT tbl_desc FROM tables WHERE tbl_name = 'MSSTAT ' AND tbl_code = ha.hastatus) AS status, ha.hauser2 as priority, ha.hauser3 as lastregref, (SELECT tbl_desc FROM tables WHERE tbl_name = 'POSPEC' AND tbl_code = po.pospec) AS specialty, (SELECT tbl_desc FROM tables WHERE tbl_name = 'POSPEC' AND tbl_code = po.posbspc1) AS subspecialty, (SELECT tbl_desc FROM tables WHERE tbl_name = 'POSPEC' AND tbl_code = po.posbspc2) AS subspec2, (SELECT tbl_desc FROM tables WHERE tbl_name = 'POSPEC' AND tbl_code = po.posbspc3) AS subspec3, po.po20, ofc.ofphone, ofc.oforgname FROM po, ofc, ha, hs, cx WHERE po.poid = ha.poid AND ha.hsid = hs.hsid AND po.poid = ofc.poid and cx.poid = po.poidand po.pospec IN ('GYN','GYO','OBG')and hs.hsid IN ('mort', 'mch', 'mdh','mease','mb')and porspartic = '1'--and cxxrefcod = 'joint replacement'ORDER BY po.polastname, po.pofirstnamPatrick Palmer |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Coalesce