|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Create View in a Select statement ?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 t**@x.com chris ch***@a.com ch***@b.com Query Result: NAME EMAIL ted t**@x.com chris ch***@a.com chris ch***@b.com Any ideas? Much Thanks, Christian K. Christian K wrote:
Show quote > Orignal Table: Select Name, Email1 as Email from Table> NAME EMAIL1 EMAIL2 > ted t**@x.com > chris ch***@a.com ch***@b.com > > > Query Result: > NAME EMAIL > ted t**@x.com > chris ch***@a.com > chris ch***@b.com > > > Any ideas? 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. Show quote "Christian K" <Christi***@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 t**@x.com > chris ch***@a.com ch***@b.com > > > Query Result: > NAME EMAIL > ted t**@x.com > chris ch***@a.com > chris ch***@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 = 'm*@com.com' ,email2 = null insert email select name = 'Dave' ,email1 = 'd***@com.com' ,email2 = 'da***@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..... Show quote "Christian K" <Christi***@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 t**@x.com > chris ch***@a.com ch***@b.com > > > Query Result: > NAME EMAIL > ted t**@x.com > chris ch***@a.com > chris ch***@b.com > > > Any ideas? > > > Much Thanks, > Christian K. > > Thank you both, it worked.
Christian K wrote: Show quote > 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 t**@x.com > chris ch***@a.com ch***@b.com > > > Query Result: > NAME EMAIL > ted t**@x.com > chris ch***@a.com > chris ch***@b.com > > > Any ideas? > > > Much Thanks, > Christian K. Thank you both, it worked.
Christian K wrote: Show quote > 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 t**@x.com > chris ch***@a.com ch***@b.com > > > Query Result: > NAME EMAIL > ted t**@x.com > chris ch***@a.com > chris ch***@b.com > > > Any ideas? > > > Much Thanks, > Christian K.  |
|||||||||||||||||||||||
Other interesting topics