Email and Website in same column, need to split

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Email and Website in same column, need to split

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-10-28 : 12:12:46

I have a column that has emails and web addresses (CONTSUPREF) in it. I want to split them out into 2 different columns. I set an alias and added another join, but I am not having any luck. They are still staying in the same column.

Can someone help me with this?

Thanks,

Wendy

Select Distinct
CONTACT2."USERDEF08",
--CONTSUPP."CONTACT" AS TYPE,
--CONTSUPP."RECTYPE",

tw."CONTSUPREF" AS WEBADDRESS,
te."CONTSUPREF" AS EMAILWEB,
--CONTSUPP."RECTYPE",
CONTACT1.ACCOUNTNO,
CONTACT1.COMPANY,
CONTACT1.CONTACT,
CONTACT1.DEPARTMENT,
CONTACT1.LASTNAME,
CONTACT1.TITLE,
CONTACT1.SOURCE,
CONTACT1.ADDRESS1,
CONTACT1.ADDRESS2,
CONTACT1.ADDRESS3,
CONTACT1.CITY,
CONTACT1.STATE,
CONTACT1.ZIP,
CONTACT1.COUNTRY as COUNTY,
CONTACT1.PHONE1,
CONTACT1.PHONE2,
CONTACT1.PHONE3 as [Prop Fee],
CONTACT1.FAX,
CONTACT1.KEY1 as [DHS REP],
CONTACT1.KEY2 as SPECIALTY,
CONTACT1.KEY3 as [Number of Docs],
CONTACT1.KEY4 as [Org Type],
CONTACT1.KEY5 as [TAX ID]

FROM
Goldmine.dbo.CONTACT1 CONTACT1
LEFT OUTER JOIN goldmine.dbo.CONTSUPP te ON
CONTACT1."ACCOUNTNO" = te."ACCOUNTNO"
--and te."CONTACT" = 'E-mail Address'

LEFT OUTER JOIN goldmine.dbo.CONTSUPP tw ON
CONTACT1."ACCOUNTNO" = tw."ACCOUNTNO"
--and te."CONTACT" = 'Web Site'---web

LEFT OUTER JOIN goldmine.dbo.CONTSUPP CONTSUPP ON
CONTACT1."ACCOUNTNO" = CONTSUPP."ACCOUNTNO"

INNER JOIN goldmine.dbo.CONTACT2 CONTACT2 ON
CONTACT2."ACCOUNTNO" = CONTACT1."ACCOUNTNO"

WHERE
te."RECTYPE" = 'P' and
(te."CONTACT" = 'E-mail Address' or tw."CONTACT" ='Web Site')

order by emailweb

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-28 : 12:20:20

Please show example data.
We cannot see if CONTSUPREF has mail AND webaddress together or only one at a time in condition to whatever.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources