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 |
|
JeffS23
Posting Yak Master
212 Posts |
 Posted - 2011-06-09 : 10:22:20
|
| My client wants Guarantor phone numbers in a file broken into 3 columns - Home, Work and Mobile. Previously, I was just grabbing the 3 phone numbers and types from the db and returning them as such. ISNULL(dbo.formatphone(g.phone1, 1), '') AS GuarantorPhone1, ISNULL(g.Phone1Type, ' ') AS GuarantorPhone1Type, ISNULL(dbo.formatphone(g.phone2, 1), '') AS GuarantorPhone2, ISNULL(g.Phone2Type, ' ') AS GuarantorPhone2Type, ISNULL(dbo.formatphone(g.phone3, 1), '') AS GuarantorPhone3, ISNULL(g.Phone3Type, ' ') AS GuarantorPhone3TypeWhat I need is three distinct fields for the 3 distinct phone types. The issue is these three phone types can be in any of these 3 fields and I would need to pull that fields value when it matches the value in the phone type. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-06-09 : 10:38:01
|
[code];with phone as( select phone = phone1, phonetype = phone1type, ID from yourtable union all select phone = phone2, phonetype = phone2type, ID from yourtable . . .)select ID, max(case when phonetype = 'Home' then phone end) as Home, max(case when phonetype = 'Work' then phone end) as Work, . . .from phonegroup by ID[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
JeffS23
Posting Yak Master
212 Posts |
Posted - 2011-06-09 : 10:49:34
|
| Thank you, that worked great. |
|
|
|
|
|
|
|
|
CASE Statement Help Needed