|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Returning selected rowsI have some test data in a table like this. Name Address State Phone1 Phone2 Phone3 Joe 2222 tx 457-7858 876-7845 369-4125 sue 7777 tx 451-8732 984-1111 235-7458 I want to return the row equal to Joe and I can easly do that like with this. SELECT * FROM TABLE1 WHERE NAME = 'JOE' I want to return 3 rows with each of the phone numbers? So the results would look like this: Joe 2222 tx 457-7858 Joe 2222 tx 876-7845 Joe 2222 tx 369-4125 thanks gv See if this is helpful.
How to rotate a table in SQL Server http://support.microsoft.com/default.aspx?scid=kb;en-us;175574 -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "gv" <viator.ge***@gmail.com> wrote in message news:OVYXhK$tGHA.4252@TK2MSFTNGP02.phx.gbl... > Hi all, > > I have some test data in a table like this. > Name Address State Phone1 Phone2 Phone3 > Joe 2222 tx 457-7858 876-7845 369-4125 > sue 7777 tx 451-8732 984-1111 235-7458 > > > I want to return the row equal to Joe and I can easly do that like with > this. > > SELECT * FROM TABLE1 WHERE NAME = 'JOE' > > I want to return 3 rows with each of the phone numbers? > So the results would look like this: > > Joe 2222 tx 457-7858 > Joe 2222 tx 876-7845 > Joe 2222 tx 369-4125 > > thanks > gv > Long day,huh :)
Show quote "Arnie Rowland" <ar***@1568.com> wrote in message news:%23NSoqM$tGHA.2172@TK2MSFTNGP05.phx.gbl... > See if this is helpful. > > How to rotate a table in SQL Server > http://support.microsoft.com/default.aspx?scid=kb;en-us;175574 > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "gv" <viator.ge***@gmail.com> wrote in message > news:OVYXhK$tGHA.4252@TK2MSFTNGP02.phx.gbl... > > Hi all, > > > > I have some test data in a table like this. > > Name Address State Phone1 Phone2 Phone3 > > Joe 2222 tx 457-7858 876-7845 369-4125 > > sue 7777 tx 451-8732 984-1111 235-7458 > > > > > > I want to return the row equal to Joe and I can easly do that like with > > this. > > > > SELECT * FROM TABLE1 WHERE NAME = 'JOE' > > > > I want to return 3 rows with each of the phone numbers? > > So the results would look like this: > > > > Joe 2222 tx 457-7858 > > Joe 2222 tx 876-7845 > > Joe 2222 tx 369-4125 > > > > thanks > > gv > > > > Slow day is more like it.
-- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Steve Dassin" <rac4sqlnospam@net> wrote in message news:O8PladCuGHA.1504@TK2MSFTNGP03.phx.gbl... > Long day,huh :) > > "Arnie Rowland" <ar***@1568.com> wrote in message > news:%23NSoqM$tGHA.2172@TK2MSFTNGP05.phx.gbl... >> See if this is helpful. >> >> How to rotate a table in SQL Server >> http://support.microsoft.com/default.aspx?scid=kb;en-us;175574 >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "gv" <viator.ge***@gmail.com> wrote in message >> news:OVYXhK$tGHA.4252@TK2MSFTNGP02.phx.gbl... >> > Hi all, >> > >> > I have some test data in a table like this. >> > Name Address State Phone1 Phone2 Phone3 >> > Joe 2222 tx 457-7858 876-7845 >> > 369-4125 >> > sue 7777 tx 451-8732 984-1111 >> > 235-7458 >> > >> > >> > I want to return the row equal to Joe and I can easly do that like with >> > this. >> > >> > SELECT * FROM TABLE1 WHERE NAME = 'JOE' >> > >> > I want to return 3 rows with each of the phone numbers? >> > So the results would look like this: >> > >> > Joe 2222 tx 457-7858 >> > Joe 2222 tx 876-7845 >> > Joe 2222 tx 369-4125 >> > >> > thanks >> > gv >> > >> >> > > This might get you started.
SELECT X.Name, CASE Y.i WHEN 1 THEN Phone1 WHEN 2 THEN Phone2 WHEN 3 THEN Phone3 END FROM TABLE1 as X CROSS JOIN (select 1 as i UNION SELECT 2 UNION SELECT 3) as Y Roy Harvey Beacon Falls, CT Show quote On Fri, 4 Aug 2006 14:20:10 -0400, "gv" <viator.ge***@gmail.com> wrote: >Hi all, > >I have some test data in a table like this. > Name Address State Phone1 Phone2 Phone3 > Joe 2222 tx 457-7858 876-7845 369-4125 > sue 7777 tx 451-8732 984-1111 235-7458 > > >I want to return the row equal to Joe and I can easly do that like with >this. > > SELECT * FROM TABLE1 WHERE NAME = 'JOE' > >I want to return 3 rows with each of the phone numbers? > So the results would look like this: > > Joe 2222 tx 457-7858 > Joe 2222 tx 876-7845 > Joe 2222 tx 369-4125 > >thanks >gv > Thanks!!! big help
In short terms what is this doing, trying to understand what you did thanks again gv Show quote "Roy Harvey" <roy_har***@snet.net> wrote in message news:h957d2pdfo35h4naacqd95au0nta5mdghv@4ax.com... > This might get you started. > > SELECT X.Name, > CASE Y.i > WHEN 1 THEN Phone1 > WHEN 2 THEN Phone2 > WHEN 3 THEN Phone3 > END > FROM TABLE1 as X > CROSS JOIN (select 1 as i UNION SELECT 2 UNION SELECT 3) as Y > > Roy Harvey > Beacon Falls, CT > > On Fri, 4 Aug 2006 14:20:10 -0400, "gv" <viator.ge***@gmail.com> > wrote: > >>Hi all, >> >>I have some test data in a table like this. >> Name Address State Phone1 Phone2 Phone3 >> Joe 2222 tx 457-7858 876-7845 369-4125 >> sue 7777 tx 451-8732 984-1111 235-7458 >> >> >>I want to return the row equal to Joe and I can easly do that like with >>this. >> >> SELECT * FROM TABLE1 WHERE NAME = 'JOE' >> >>I want to return 3 rows with each of the phone numbers? >> So the results would look like this: >> >> Joe 2222 tx 457-7858 >> Joe 2222 tx 876-7845 >> Joe 2222 tx 369-4125 >> >>thanks >>gv >> The subquery that appears in the FROM list of the outer query is a
Derived Table - a query that returns a table as a result. It returns a table with three rows: 1, 2, 3. The outer query has a CROSS JOIN. That effectively means that every row in the first table (TABLE1) is matched with every row in the second table (Y). So you get to "look at" each row in your table 3 times, and get 3 rows in the result set for every row in TABLE1. The CASE expression controlls which phone number appears in the result set. Since each row in TABLE is matched up with each of (1,2,3), this associates that each of those numbers with a specific phone number. Hope that clarifies things a bit. Roy Show quote On Fri, 4 Aug 2006 15:27:47 -0400, "gv" <viator.ge***@gmail.com> wrote: >Thanks!!! big help > >In short terms what is this doing, trying to understand what you did > >thanks again >gv > > >"Roy Harvey" <roy_har***@snet.net> wrote in message >news:h957d2pdfo35h4naacqd95au0nta5mdghv@4ax.com... >> This might get you started. >> >> SELECT X.Name, >> CASE Y.i >> WHEN 1 THEN Phone1 >> WHEN 2 THEN Phone2 >> WHEN 3 THEN Phone3 >> END >> FROM TABLE1 as X >> CROSS JOIN (select 1 as i UNION SELECT 2 UNION SELECT 3) as Y >> >> Roy Harvey >> Beacon Falls, CT >> >> On Fri, 4 Aug 2006 14:20:10 -0400, "gv" <viator.ge***@gmail.com> >> wrote: >> >>>Hi all, >>> >>>I have some test data in a table like this. >>> Name Address State Phone1 Phone2 Phone3 >>> Joe 2222 tx 457-7858 876-7845 369-4125 >>> sue 7777 tx 451-8732 984-1111 235-7458 >>> >>> >>>I want to return the row equal to Joe and I can easly do that like with >>>this. >>> >>> SELECT * FROM TABLE1 WHERE NAME = 'JOE' >>> >>>I want to return 3 rows with each of the phone numbers? >>> So the results would look like this: >>> >>> Joe 2222 tx 457-7858 >>> Joe 2222 tx 876-7845 >>> Joe 2222 tx 369-4125 >>> >>>thanks >>>gv >>> > Thanks again!!!
one more question to add a seperate column next to it with a value based on each one of those columns could I do that. so the results could be like this Joe 2222 tx 457-7858 A Joe 2222 tx 876-7845 B Joe 2222 tx 369-4125 C thanks gv Show quote "Roy Harvey" <roy_har***@snet.net> wrote in message news:ov87d2pnesfcref50715mi4h21lf0qq90g@4ax.com... > The subquery that appears in the FROM list of the outer query is a > Derived Table - a query that returns a table as a result. It returns > a table with three rows: 1, 2, 3. > > The outer query has a CROSS JOIN. That effectively means that every > row in the first table (TABLE1) is matched with every row in the > second table (Y). So you get to "look at" each row in your table 3 > times, and get 3 rows in the result set for every row in TABLE1. > > The CASE expression controlls which phone number appears in the result > set. Since each row in TABLE is matched up with each of (1,2,3), this > associates that each of those numbers with a specific phone number. > > Hope that clarifies things a bit. > > Roy > > On Fri, 4 Aug 2006 15:27:47 -0400, "gv" <viator.ge***@gmail.com> > wrote: > >>Thanks!!! big help >> >>In short terms what is this doing, trying to understand what you did >> >>thanks again >>gv >> >> >>"Roy Harvey" <roy_har***@snet.net> wrote in message >>news:h957d2pdfo35h4naacqd95au0nta5mdghv@4ax.com... >>> This might get you started. >>> >>> SELECT X.Name, >>> CASE Y.i >>> WHEN 1 THEN Phone1 >>> WHEN 2 THEN Phone2 >>> WHEN 3 THEN Phone3 >>> END >>> FROM TABLE1 as X >>> CROSS JOIN (select 1 as i UNION SELECT 2 UNION SELECT 3) as Y >>> >>> Roy Harvey >>> Beacon Falls, CT >>> >>> On Fri, 4 Aug 2006 14:20:10 -0400, "gv" <viator.ge***@gmail.com> >>> wrote: >>> >>>>Hi all, >>>> >>>>I have some test data in a table like this. >>>> Name Address State Phone1 Phone2 Phone3 >>>> Joe 2222 tx 457-7858 876-7845 >>>> 369-4125 >>>> sue 7777 tx 451-8732 984-1111 >>>> 235-7458 >>>> >>>> >>>>I want to return the row equal to Joe and I can easly do that like with >>>>this. >>>> >>>> SELECT * FROM TABLE1 WHERE NAME = 'JOE' >>>> >>>>I want to return 3 rows with each of the phone numbers? >>>> So the results would look like this: >>>> >>>> Joe 2222 tx 457-7858 >>>> Joe 2222 tx 876-7845 >>>> Joe 2222 tx 369-4125 >>>> >>>>thanks >>>>gv >>>> >> No problem. Just add another CASE expression, and have it return 'A',
'B', or 'C' as appropriate. Roy Show quote On Fri, 4 Aug 2006 15:58:52 -0400, "gv" <viator.ge***@gmail.com> wrote: >Thanks again!!! > >one more question to add a seperate column next to it with a value >based on each one of those columns could I do that. > >so the results could be like this > Joe 2222 tx 457-7858 A > Joe 2222 tx 876-7845 B > Joe 2222 tx 369-4125 C > > >thanks >gv > > >"Roy Harvey" <roy_har***@snet.net> wrote in message >news:ov87d2pnesfcref50715mi4h21lf0qq90g@4ax.com... >> The subquery that appears in the FROM list of the outer query is a >> Derived Table - a query that returns a table as a result. It returns >> a table with three rows: 1, 2, 3. >> >> The outer query has a CROSS JOIN. That effectively means that every >> row in the first table (TABLE1) is matched with every row in the >> second table (Y). So you get to "look at" each row in your table 3 >> times, and get 3 rows in the result set for every row in TABLE1. >> >> The CASE expression controlls which phone number appears in the result >> set. Since each row in TABLE is matched up with each of (1,2,3), this >> associates that each of those numbers with a specific phone number. >> >> Hope that clarifies things a bit. >> >> Roy >> >> On Fri, 4 Aug 2006 15:27:47 -0400, "gv" <viator.ge***@gmail.com> >> wrote: >> >>>Thanks!!! big help >>> >>>In short terms what is this doing, trying to understand what you did >>> >>>thanks again >>>gv >>> >>> >>>"Roy Harvey" <roy_har***@snet.net> wrote in message >>>news:h957d2pdfo35h4naacqd95au0nta5mdghv@4ax.com... >>>> This might get you started. >>>> >>>> SELECT X.Name, >>>> CASE Y.i >>>> WHEN 1 THEN Phone1 >>>> WHEN 2 THEN Phone2 >>>> WHEN 3 THEN Phone3 >>>> END >>>> FROM TABLE1 as X >>>> CROSS JOIN (select 1 as i UNION SELECT 2 UNION SELECT 3) as Y >>>> >>>> Roy Harvey >>>> Beacon Falls, CT >>>> >>>> On Fri, 4 Aug 2006 14:20:10 -0400, "gv" <viator.ge***@gmail.com> >>>> wrote: >>>> >>>>>Hi all, >>>>> >>>>>I have some test data in a table like this. >>>>> Name Address State Phone1 Phone2 Phone3 >>>>> Joe 2222 tx 457-7858 876-7845 >>>>> 369-4125 >>>>> sue 7777 tx 451-8732 984-1111 >>>>> 235-7458 >>>>> >>>>> >>>>>I want to return the row equal to Joe and I can easly do that like with >>>>>this. >>>>> >>>>> SELECT * FROM TABLE1 WHERE NAME = 'JOE' >>>>> >>>>>I want to return 3 rows with each of the phone numbers? >>>>> So the results would look like this: >>>>> >>>>> Joe 2222 tx 457-7858 >>>>> Joe 2222 tx 876-7845 >>>>> Joe 2222 tx 369-4125 >>>>> >>>>>thanks >>>>>gv >>>>> >>> > For that you could modify Roy's answer to:
SELECT X.Name, X.Address, X.State, CASE Y.i WHEN 1 THEN Phone1 WHEN 2 THEN Phone2 WHEN 3 THEN Phone3 END, Y.i FROM TABLE1 as X CROSS JOIN (select 'A' as i UNION SELECT 'B' UNION SELECT 'C') as Y Tom Show quote "gv" <viator.ge***@gmail.com> wrote in message news:O9cbrBAuGHA.476@TK2MSFTNGP06.phx.gbl... > Thanks again!!! > > one more question to add a seperate column next to it with a value > based on each one of those columns could I do that. > > so the results could be like this > Joe 2222 tx 457-7858 A > Joe 2222 tx 876-7845 B > Joe 2222 tx 369-4125 C > > > thanks > gv > > > "Roy Harvey" <roy_har***@snet.net> wrote in message > news:ov87d2pnesfcref50715mi4h21lf0qq90g@4ax.com... >> The subquery that appears in the FROM list of the outer query is a >> Derived Table - a query that returns a table as a result. It returns >> a table with three rows: 1, 2, 3. >> >> The outer query has a CROSS JOIN. That effectively means that every >> row in the first table (TABLE1) is matched with every row in the >> second table (Y). So you get to "look at" each row in your table 3 >> times, and get 3 rows in the result set for every row in TABLE1. >> >> The CASE expression controlls which phone number appears in the result >> set. Since each row in TABLE is matched up with each of (1,2,3), this >> associates that each of those numbers with a specific phone number. >> >> Hope that clarifies things a bit. >> >> Roy >> >> On Fri, 4 Aug 2006 15:27:47 -0400, "gv" <viator.ge***@gmail.com> >> wrote: >> >>>Thanks!!! big help >>> >>>In short terms what is this doing, trying to understand what you did >>> >>>thanks again >>>gv >>> >>> >>>"Roy Harvey" <roy_har***@snet.net> wrote in message >>>news:h957d2pdfo35h4naacqd95au0nta5mdghv@4ax.com... >>>> This might get you started. >>>> >>>> SELECT X.Name, >>>> CASE Y.i >>>> WHEN 1 THEN Phone1 >>>> WHEN 2 THEN Phone2 >>>> WHEN 3 THEN Phone3 >>>> END >>>> FROM TABLE1 as X >>>> CROSS JOIN (select 1 as i UNION SELECT 2 UNION SELECT 3) as Y >>>> >>>> Roy Harvey >>>> Beacon Falls, CT >>>> >>>> On Fri, 4 Aug 2006 14:20:10 -0400, "gv" <viator.ge***@gmail.com> >>>> wrote: >>>> >>>>>Hi all, >>>>> >>>>>I have some test data in a table like this. >>>>> Name Address State Phone1 Phone2 Phone3 >>>>> Joe 2222 tx 457-7858 876-7845 369-4125 >>>>> sue 7777 tx 451-8732 984-1111 235-7458 >>>>> >>>>> >>>>>I want to return the row equal to Joe and I can easly do that like with >>>>>this. >>>>> >>>>> SELECT * FROM TABLE1 WHERE NAME = 'JOE' >>>>> >>>>>I want to return 3 rows with each of the phone numbers? >>>>> So the results would look like this: >>>>> >>>>> Joe 2222 tx 457-7858 >>>>> Joe 2222 tx 876-7845 >>>>> Joe 2222 tx 369-4125 >>>>> >>>>>thanks >>>>>gv >>>>> >>> > > If you changed the CASE expression too that would work fine.
SELECT X.Name, X.Address, X.State, CASE Y.i WHEN 'A' THEN Phone1 WHEN 'B' THEN Phone2 WHEN 'C' THEN Phone3 END, Y.i FROM TABLE1 as X CROSS JOIN (select 'A' as i UNION SELECT 'B' UNION SELECT 'C') as Y Roy Harvey Beacon Falls, CT On Fri, 4 Aug 2006 16:09:03 -0400, "Tom Cooper" <tom.no.spam.please.cooper@comcast.net> wrote: Show quote >For that you could modify Roy's answer to: > >SELECT X.Name, > X.Address, > X.State, > CASE Y.i > WHEN 1 THEN Phone1 > WHEN 2 THEN Phone2 > WHEN 3 THEN Phone3 > END, > Y.i > FROM TABLE1 as X > CROSS JOIN (select 'A' as i UNION SELECT 'B' UNION SELECT 'C') as Y > >Tom > >"gv" <viator.ge***@gmail.com> wrote in message >news:O9cbrBAuGHA.476@TK2MSFTNGP06.phx.gbl... >> Thanks again!!! >> >> one more question to add a seperate column next to it with a value >> based on each one of those columns could I do that. >> >> so the results could be like this >> Joe 2222 tx 457-7858 A >> Joe 2222 tx 876-7845 B >> Joe 2222 tx 369-4125 C >> >> >> thanks >> gv >> >> >> "Roy Harvey" <roy_har***@snet.net> wrote in message >> news:ov87d2pnesfcref50715mi4h21lf0qq90g@4ax.com... >>> The subquery that appears in the FROM list of the outer query is a >>> Derived Table - a query that returns a table as a result. It returns >>> a table with three rows: 1, 2, 3. >>> >>> The outer query has a CROSS JOIN. That effectively means that every >>> row in the first table (TABLE1) is matched with every row in the >>> second table (Y). So you get to "look at" each row in your table 3 >>> times, and get 3 rows in the result set for every row in TABLE1. >>> >>> The CASE expression controlls which phone number appears in the result >>> set. Since each row in TABLE is matched up with each of (1,2,3), this >>> associates that each of those numbers with a specific phone number. >>> >>> Hope that clarifies things a bit. >>> >>> Roy >>> >>> On Fri, 4 Aug 2006 15:27:47 -0400, "gv" <viator.ge***@gmail.com> >>> wrote: >>> >>>>Thanks!!! big help >>>> >>>>In short terms what is this doing, trying to understand what you did >>>> >>>>thanks again >>>>gv >>>> >>>> >>>>"Roy Harvey" <roy_har***@snet.net> wrote in message >>>>news:h957d2pdfo35h4naacqd95au0nta5mdghv@4ax.com... >>>>> This might get you started. >>>>> >>>>> SELECT X.Name, >>>>> CASE Y.i >>>>> WHEN 1 THEN Phone1 >>>>> WHEN 2 THEN Phone2 >>>>> WHEN 3 THEN Phone3 >>>>> END >>>>> FROM TABLE1 as X >>>>> CROSS JOIN (select 1 as i UNION SELECT 2 UNION SELECT 3) as Y >>>>> >>>>> Roy Harvey >>>>> Beacon Falls, CT >>>>> >>>>> On Fri, 4 Aug 2006 14:20:10 -0400, "gv" <viator.ge***@gmail.com> >>>>> wrote: >>>>> >>>>>>Hi all, >>>>>> >>>>>>I have some test data in a table like this. >>>>>> Name Address State Phone1 Phone2 Phone3 >>>>>> Joe 2222 tx 457-7858 876-7845 369-4125 >>>>>> sue 7777 tx 451-8732 984-1111 235-7458 >>>>>> >>>>>> >>>>>>I want to return the row equal to Joe and I can easly do that like with >>>>>>this. >>>>>> >>>>>> SELECT * FROM TABLE1 WHERE NAME = 'JOE' >>>>>> >>>>>>I want to return 3 rows with each of the phone numbers? >>>>>> So the results would look like this: >>>>>> >>>>>> Joe 2222 tx 457-7858 >>>>>> Joe 2222 tx 876-7845 >>>>>> Joe 2222 tx 369-4125 >>>>>> >>>>>>thanks >>>>>>gv >>>>>> >>>> >> >> > Thanks all!!!
I fixed my problem. Works great.. have a great weekend... :<) thanks gv Show quote "Roy Harvey" <roy_har***@snet.net> wrote in message news:ika7d2hon211d6p5s2prsnd8i2ugpuqhh2@4ax.com... > If you changed the CASE expression too that would work fine. > > SELECT X.Name, > X.Address, > X.State, > CASE Y.i > WHEN 'A' THEN Phone1 > WHEN 'B' THEN Phone2 > WHEN 'C' THEN Phone3 > END, > Y.i > FROM TABLE1 as X > CROSS JOIN (select 'A' as i UNION SELECT 'B' UNION SELECT 'C') as Y > > Roy Harvey > Beacon Falls, CT > > On Fri, 4 Aug 2006 16:09:03 -0400, "Tom Cooper" > <tom.no.spam.please.cooper@comcast.net> wrote: > >>For that you could modify Roy's answer to: >> >>SELECT X.Name, >> X.Address, >> X.State, >> CASE Y.i >> WHEN 1 THEN Phone1 >> WHEN 2 THEN Phone2 >> WHEN 3 THEN Phone3 >> END, >> Y.i >> FROM TABLE1 as X >> CROSS JOIN (select 'A' as i UNION SELECT 'B' UNION SELECT 'C') as Y >> >>Tom >> >>"gv" <viator.ge***@gmail.com> wrote in message >>news:O9cbrBAuGHA.476@TK2MSFTNGP06.phx.gbl... >>> Thanks again!!! >>> >>> one more question to add a seperate column next to it with a value >>> based on each one of those columns could I do that. >>> >>> so the results could be like this >>> Joe 2222 tx 457-7858 A >>> Joe 2222 tx 876-7845 B >>> Joe 2222 tx 369-4125 C >>> >>> >>> thanks >>> gv >>> >>> >>> "Roy Harvey" <roy_har***@snet.net> wrote in message >>> news:ov87d2pnesfcref50715mi4h21lf0qq90g@4ax.com... >>>> The subquery that appears in the FROM list of the outer query is a >>>> Derived Table - a query that returns a table as a result. It returns >>>> a table with three rows: 1, 2, 3. >>>> >>>> The outer query has a CROSS JOIN. That effectively means that every >>>> row in the first table (TABLE1) is matched with every row in the >>>> second table (Y). So you get to "look at" each row in your table 3 >>>> times, and get 3 rows in the result set for every row in TABLE1. >>>> >>>> The CASE expression controlls which phone number appears in the result >>>> set. Since each row in TABLE is matched up with each of (1,2,3), this >>>> associates that each of those numbers with a specific phone number. >>>> >>>> Hope that clarifies things a bit. >>>> >>>> Roy >>>> >>>> On Fri, 4 Aug 2006 15:27:47 -0400, "gv" <viator.ge***@gmail.com> >>>> wrote: >>>> >>>>>Thanks!!! big help >>>>> >>>>>In short terms what is this doing, trying to understand what you did >>>>> >>>>>thanks again >>>>>gv >>>>> >>>>> >>>>>"Roy Harvey" <roy_har***@snet.net> wrote in message >>>>>news:h957d2pdfo35h4naacqd95au0nta5mdghv@4ax.com... >>>>>> This might get you started. >>>>>> >>>>>> SELECT X.Name, >>>>>> CASE Y.i >>>>>> WHEN 1 THEN Phone1 >>>>>> WHEN 2 THEN Phone2 >>>>>> WHEN 3 THEN Phone3 >>>>>> END >>>>>> FROM TABLE1 as X >>>>>> CROSS JOIN (select 1 as i UNION SELECT 2 UNION SELECT 3) as Y >>>>>> >>>>>> Roy Harvey >>>>>> Beacon Falls, CT >>>>>> >>>>>> On Fri, 4 Aug 2006 14:20:10 -0400, "gv" <viator.ge***@gmail.com> >>>>>> wrote: >>>>>> >>>>>>>Hi all, >>>>>>> >>>>>>>I have some test data in a table like this. >>>>>>> Name Address State Phone1 Phone2 >>>>>>> Phone3 >>>>>>> Joe 2222 tx 457-7858 876-7845 369-4125 >>>>>>> sue 7777 tx 451-8732 984-1111 235-7458 >>>>>>> >>>>>>> >>>>>>>I want to return the row equal to Joe and I can easly do that like >>>>>>>with >>>>>>>this. >>>>>>> >>>>>>> SELECT * FROM TABLE1 WHERE NAME = 'JOE' >>>>>>> >>>>>>>I want to return 3 rows with each of the phone numbers? >>>>>>> So the results would look like this: >>>>>>> >>>>>>> Joe 2222 tx 457-7858 >>>>>>> Joe 2222 tx 876-7845 >>>>>>> Joe 2222 tx 369-4125 >>>>>>> >>>>>>>thanks >>>>>>>gv >>>>>>> >>>>> >>> >>> >> Thanks again everyone...
I get this error Server: Msg 245, Level 16, State 1, Line 14 Syntax error converting the varchar value 'A' to a column of data type int. thanks gv Show quote "Tom Cooper" <tom.no.spam.please.cooper@comcast.net> wrote in message news:P-idneExfv7DNE7ZnZ2dnUVZ_smdnZ2d@comcast.com... > For that you could modify Roy's answer to: > > SELECT X.Name, > X.Address, > X.State, > CASE Y.i > WHEN 1 THEN Phone1 > WHEN 2 THEN Phone2 > WHEN 3 THEN Phone3 > END, > Y.i > FROM TABLE1 as X > CROSS JOIN (select 'A' as i UNION SELECT 'B' UNION SELECT 'C') as Y > > Tom > > "gv" <viator.ge***@gmail.com> wrote in message > news:O9cbrBAuGHA.476@TK2MSFTNGP06.phx.gbl... >> Thanks again!!! >> >> one more question to add a seperate column next to it with a value >> based on each one of those columns could I do that. >> >> so the results could be like this >> Joe 2222 tx 457-7858 A >> Joe 2222 tx 876-7845 B >> Joe 2222 tx 369-4125 C >> >> >> thanks >> gv >> >> >> "Roy Harvey" <roy_har***@snet.net> wrote in message >> news:ov87d2pnesfcref50715mi4h21lf0qq90g@4ax.com... >>> The subquery that appears in the FROM list of the outer query is a >>> Derived Table - a query that returns a table as a result. It returns >>> a table with three rows: 1, 2, 3. >>> >>> The outer query has a CROSS JOIN. That effectively means that every >>> row in the first table (TABLE1) is matched with every row in the >>> second table (Y). So you get to "look at" each row in your table 3 >>> times, and get 3 rows in the result set for every row in TABLE1. >>> >>> The CASE expression controlls which phone number appears in the result >>> set. Since each row in TABLE is matched up with each of (1,2,3), this >>> associates that each of those numbers with a specific phone number. >>> >>> Hope that clarifies things a bit. >>> >>> Roy >>> >>> On Fri, 4 Aug 2006 15:27:47 -0400, "gv" <viator.ge***@gmail.com> >>> wrote: >>> >>>>Thanks!!! big help >>>> >>>>In short terms what is this doing, trying to understand what you did >>>> >>>>thanks again >>>>gv >>>> >>>> >>>>"Roy Harvey" <roy_har***@snet.net> wrote in message >>>>news:h957d2pdfo35h4naacqd95au0nta5mdghv@4ax.com... >>>>> This might get you started. >>>>> >>>>> SELECT X.Name, >>>>> CASE Y.i >>>>> WHEN 1 THEN Phone1 >>>>> WHEN 2 THEN Phone2 >>>>> WHEN 3 THEN Phone3 >>>>> END >>>>> FROM TABLE1 as X >>>>> CROSS JOIN (select 1 as i UNION SELECT 2 UNION SELECT 3) as Y >>>>> >>>>> Roy Harvey >>>>> Beacon Falls, CT >>>>> >>>>> On Fri, 4 Aug 2006 14:20:10 -0400, "gv" <viator.ge***@gmail.com> >>>>> wrote: >>>>> >>>>>>Hi all, >>>>>> >>>>>>I have some test data in a table like this. >>>>>> Name Address State Phone1 Phone2 >>>>>> Phone3 >>>>>> Joe 2222 tx 457-7858 876-7845 369-4125 >>>>>> sue 7777 tx 451-8732 984-1111 235-7458 >>>>>> >>>>>> >>>>>>I want to return the row equal to Joe and I can easly do that like >>>>>>with >>>>>>this. >>>>>> >>>>>> SELECT * FROM TABLE1 WHERE NAME = 'JOE' >>>>>> >>>>>>I want to return 3 rows with each of the phone numbers? >>>>>> So the results would look like this: >>>>>> >>>>>> Joe 2222 tx 457-7858 >>>>>> Joe 2222 tx 876-7845 >>>>>> Joe 2222 tx 369-4125 >>>>>> >>>>>>thanks >>>>>>gv >>>>>> >>>> >> >> > > Sorry, it should have been:
SELECT X.Name, X.Address, X.State, CASE Y.i WHEN 'A' THEN Phone1 WHEN 'B' THEN Phone2 WHEN 'C' THEN Phone3 END, Y.i FROM TABLE1 as X CROSS JOIN (select 'A' as i UNION ALL SELECT 'B' UNION ALL SELECT 'C') as Y Tom Show quote "gv" <viator.ge***@gmail.com> wrote in message news:%23t2ZiOAuGHA.2172@TK2MSFTNGP05.phx.gbl... > Thanks again everyone... > I get this error > > Server: Msg 245, Level 16, State 1, Line 14 > Syntax error converting the varchar value 'A' to a column of data type > int. > > thanks > gv > > > > "Tom Cooper" <tom.no.spam.please.cooper@comcast.net> wrote in message > news:P-idneExfv7DNE7ZnZ2dnUVZ_smdnZ2d@comcast.com... >> For that you could modify Roy's answer to: >> >> SELECT X.Name, >> X.Address, >> X.State, >> CASE Y.i >> WHEN 1 THEN Phone1 >> WHEN 2 THEN Phone2 >> WHEN 3 THEN Phone3 >> END, >> Y.i >> FROM TABLE1 as X >> CROSS JOIN (select 'A' as i UNION SELECT 'B' UNION SELECT 'C') as Y >> >> Tom >> >> "gv" <viator.ge***@gmail.com> wrote in message >> news:O9cbrBAuGHA.476@TK2MSFTNGP06.phx.gbl... >>> Thanks again!!! >>> >>> one more question to add a seperate column next to it with a value >>> based on each one of those columns could I do that. >>> >>> so the results could be like this >>> Joe 2222 tx 457-7858 A >>> Joe 2222 tx 876-7845 B >>> Joe 2222 tx 369-4125 C >>> >>> >>> thanks >>> gv >>> >>> >>> "Roy Harvey" <roy_har***@snet.net> wrote in message >>> news:ov87d2pnesfcref50715mi4h21lf0qq90g@4ax.com... >>>> The subquery that appears in the FROM list of the outer query is a >>>> Derived Table - a query that returns a table as a result. It returns >>>> a table with three rows: 1, 2, 3. >>>> >>>> The outer query has a CROSS JOIN. That effectively means that every >>>> row in the first table (TABLE1) is matched with every row in the >>>> second table (Y). So you get to "look at" each row in your table 3 >>>> times, and get 3 rows in the result set for every row in TABLE1. >>>> >>>> The CASE expression controlls which phone number appears in the result >>>> set. Since each row in TABLE is matched up with each of (1,2,3), this >>>> associates that each of those numbers with a specific phone number. >>>> >>>> Hope that clarifies things a bit. >>>> >>>> Roy >>>> >>>> On Fri, 4 Aug 2006 15:27:47 -0400, "gv" <viator.ge***@gmail.com> >>>> wrote: >>>> >>>>>Thanks!!! big help >>>>> >>>>>In short terms what is this doing, trying to understand what you did >>>>> >>>>>thanks again >>>>>gv >>>>> >>>>> >>>>>"Roy Harvey" <roy_har***@snet.net> wrote in message >>>>>news:h957d2pdfo35h4naacqd95au0nta5mdghv@4ax.com... >>>>>> This might get you started. >>>>>> >>>>>> SELECT X.Name, >>>>>> CASE Y.i >>>>>> WHEN 1 THEN Phone1 >>>>>> WHEN 2 THEN Phone2 >>>>>> WHEN 3 THEN Phone3 >>>>>> END >>>>>> FROM TABLE1 as X >>>>>> CROSS JOIN (select 1 as i UNION SELECT 2 UNION SELECT 3) as Y >>>>>> >>>>>> Roy Harvey >>>>>> Beacon Falls, CT >>>>>> >>>>>> On Fri, 4 Aug 2006 14:20:10 -0400, "gv" <viator.ge***@gmail.com> >>>>>> wrote: >>>>>> >>>>>>>Hi all, >>>>>>> >>>>>>>I have some test data in a table like this. >>>>>>> Name Address State Phone1 Phone2 Phone3 >>>>>>> Joe 2222 tx 457-7858 876-7845 369-4125 >>>>>>> sue 7777 tx 451-8732 984-1111 235-7458 >>>>>>> >>>>>>> >>>>>>>I want to return the row equal to Joe and I can easly do that like >>>>>>>with >>>>>>>this. >>>>>>> >>>>>>> SELECT * FROM TABLE1 WHERE NAME = 'JOE' >>>>>>> >>>>>>>I want to return 3 rows with each of the phone numbers? >>>>>>> So the results would look like this: >>>>>>> >>>>>>> Joe 2222 tx 457-7858 >>>>>>> Joe 2222 tx 876-7845 >>>>>>> Joe 2222 tx 369-4125 >>>>>>> >>>>>>>thanks >>>>>>>gv >>>>>>> >>>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics