Create View in a Select statement ?

Greetings all. I'm a complete SQL novice, and well, I'm not even using
SQL, but I have a dBase control which utilizes SQL SELECT statements to
pull a rowset from a .DBF table.
I have a small table with a few fields, but for this query I only need
to deal with the fields NAME, EMAIL1, and EMAIL2. Is there a way for
the SELECT statement to return a rowset with the fields NAME and EMAIL,
where each entry for EMAIL1 or EMAIL2 will be output to it's own row.
example--
Orignal Table:
NAME EMAIL1 EMAIL2
ted ted@.x.com
chris chris@.a.com chris@.b.com
Query Result:
NAME EMAIL
ted ted@.x.com
chris chris@.a.com
chris chris@.b.com
Any ideas?
Much Thanks,
Christian K.Christian K wrote:

> Orignal Table:
> NAME EMAIL1 EMAIL2
> ted ted@.x.com
> chris chris@.a.com chris@.b.com
>
> Query Result:
> NAME EMAIL
> ted ted@.x.com
> chris chris@.a.com
> chris chris@.b.com
>
> Any ideas?
Select Name, Email1 as Email from Table
Union
Select Name, Email2 as Email from Table where Email2 IS NOT NULL
HTH,
Stijn Verrept.|||select name, email1 as email from table
union
select name, email2 as email from table
order by name
This will eliminate duplicates as for example when, let's say Ted, has the
same email in email1 and email2.
Use Union All is you don't want to eliminate duplicates.
"Christian K" <ChristianK@.HeroData.com> wrote in message
news:ua%23YskD$FHA.2040@.TK2MSFTNGP14.phx.gbl...
> Greetings all. I'm a complete SQL novice, and well, I'm not even using
> SQL, but I have a dBase control which utilizes SQL SELECT statements to
> pull a rowset from a .DBF table.
> I have a small table with a few fields, but for this query I only need
> to deal with the fields NAME, EMAIL1, and EMAIL2. Is there a way for
> the SELECT statement to return a rowset with the fields NAME and EMAIL,
> where each entry for EMAIL1 or EMAIL2 will be output to it's own row.
> example--
> Orignal Table:
> NAME EMAIL1 EMAIL2
> ted ted@.x.com
> chris chris@.a.com chris@.b.com
>
> Query Result:
> NAME EMAIL
> ted ted@.x.com
> chris chris@.a.com
> chris chris@.b.com
>
> Any ideas?
>
> Much Thanks,
> Christian K.
>|||If your SQL qry component will enable you to use the keyword union, here is
a
way...
create table email (name varchar(20) ,email1 varchar(50) ,email2 varchar(50)
)
insert email
select name = 'Jeff' ,email1 = 'me@.com.com' ,email2 = null
insert email
select name = 'Dave' ,email1 = 'dave@.com.com' ,email2 = 'david@.me.com'
select name ,email = email1
from email
where email1 is not null
union
select name ,email = email2
from email
where email2 is not null
order by name
drop table email
HTH
JeffP....
"Christian K" <ChristianK@.HeroData.com> wrote in message
news:ua%23YskD$FHA.2040@.TK2MSFTNGP14.phx.gbl...
> Greetings all. I'm a complete SQL novice, and well, I'm not even using
> SQL, but I have a dBase control which utilizes SQL SELECT statements to
> pull a rowset from a .DBF table.
> I have a small table with a few fields, but for this query I only need
> to deal with the fields NAME, EMAIL1, and EMAIL2. Is there a way for
> the SELECT statement to return a rowset with the fields NAME and EMAIL,
> where each entry for EMAIL1 or EMAIL2 will be output to it's own row.
> example--
> Orignal Table:
> NAME EMAIL1 EMAIL2
> ted ted@.x.com
> chris chris@.a.com chris@.b.com
>
> Query Result:
> NAME EMAIL
> ted ted@.x.com
> chris chris@.a.com
> chris chris@.b.com
>
> Any ideas?
>
> Much Thanks,
> Christian K.
>|||Thank you both, it worked.
Christian K wrote:

> Greetings all. I'm a complete SQL novice, and well, I'm not even using
> SQL, but I have a dBase control which utilizes SQL SELECT statements
> to pull a rowset from a .DBF table.
> I have a small table with a few fields, but for this query I only need
> to deal with the fields NAME, EMAIL1, and EMAIL2. Is there a way for
> the SELECT statement to return a rowset with the fields NAME and
> EMAIL, where each entry for EMAIL1 or EMAIL2 will be output to it's
> own row.
> example--
> Orignal Table:
> NAME EMAIL1 EMAIL2
> ted ted@.x.com
> chris chris@.a.com chris@.b.com
>
> Query Result:
> NAME EMAIL
> ted ted@.x.com
> chris chris@.a.com
> chris chris@.b.com
>
> Any ideas?
>
> Much Thanks,
> Christian K.|||Thank you both, it worked.
Christian K wrote:

> Greetings all. I'm a complete SQL novice, and well, I'm not even using
> SQL, but I have a dBase control which utilizes SQL SELECT statements
> to pull a rowset from a .DBF table.
> I have a small table with a few fields, but for this query I only need
> to deal with the fields NAME, EMAIL1, and EMAIL2. Is there a way for
> the SELECT statement to return a rowset with the fields NAME and
> EMAIL, where each entry for EMAIL1 or EMAIL2 will be output to it's
> own row.
> example--
> Orignal Table:
> NAME EMAIL1 EMAIL2
> ted ted@.x.com
> chris chris@.a.com chris@.b.com
>
> Query Result:
> NAME EMAIL
> ted ted@.x.com
> chris chris@.a.com
> chris chris@.b.com
>
> Any ideas?
>
> Much Thanks,
> Christian K.