|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Returning customised strings in a columnI'm fairly new to SQL Server, but have at least basic knowledge of SQL (as language). Building a web application I normally process retrieved data directly in code, but after having read about and begun to realize the power of stored procedures, I'm sure there are better ways to solve this problem. Let's say I have three tables; Persons, Cars and PersonCars. I assume you understand this pretty straight-forward notation: Persons: PersonId, Name, Phone Cars: CarId, Registration PersonCars: fkPersonId, fkCarId Now, I'd like to return a recordset containing each individual Person and registration numbers of the cars that that person owns. For example: By using a standard joined query I can get the following results: Name; Phone; Registration John Smith; 5551234; ABC123 John Smith; 5551234; BCD234 John Smith; 5551234; GHI665 But what I really want is this: Name; Phone; Registrations John Smith; 5551234; ABC123, BCD234, GHI665 Is this at all possible? Should I use a SP, a view or some kind of user-defined function? Or should I rather process this in code by querying separately for all the person's car, while iterating over each row in the recordset? Thanks a lot in advance, Christopher Carlander Why don't you let the presentation tier handle the presentation?
Show quote "Christopher Carlander" <pt95***@removethis.yahoo.com> wrote in message news:e1OK1XX1FHA.1028@TK2MSFTNGP12.phx.gbl... > Hi, > > I'm fairly new to SQL Server, but have at least basic knowledge of SQL (as > language). Building a web application I normally process retrieved data > directly in code, but after having read about and begun to realize the > power > of stored procedures, I'm sure there are better ways to solve this > problem. > > Let's say I have three tables; Persons, Cars and PersonCars. I assume you > understand this pretty straight-forward notation: > Persons: PersonId, Name, Phone > Cars: CarId, Registration > PersonCars: fkPersonId, fkCarId > > Now, I'd like to return a recordset containing each individual Person and > registration numbers of the cars that that person owns. > > For example: > By using a standard joined query I can get the following results: > Name; Phone; Registration > John Smith; 5551234; ABC123 > John Smith; 5551234; BCD234 > John Smith; 5551234; GHI665 > > But what I really want is this: > Name; Phone; Registrations > John Smith; 5551234; ABC123, BCD234, GHI665 > > Is this at all possible? Should I use a SP, a view or some kind of > user-defined function? Or should I rather process this in code by querying > separately for all the person's car, while iterating over each row in the > recordset? > > Thanks a lot in advance, > Christopher Carlander > > Actually I was just thinking in terms of encapsulation. All calls to
retrieve this recordset should always be formatted in the same way, regardless of presentation platform (be it PHP or ASP.NET). But of course it can be solved on the client side instead. I'm just keen to learn new approaches. :) /Chris ### Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:uQCcSaX1FHA.2964@TK2MSFTNGP09.phx.gbl... > Why don't you let the presentation tier handle the presentation? > > > "Christopher Carlander" <pt95***@removethis.yahoo.com> wrote in message > news:e1OK1XX1FHA.1028@TK2MSFTNGP12.phx.gbl... > > Hi, > > > > I'm fairly new to SQL Server, but have at least basic knowledge of SQL (as > > language). Building a web application I normally process retrieved data > > directly in code, but after having read about and begun to realize the > > power > > of stored procedures, I'm sure there are better ways to solve this > > problem. > > > > Let's say I have three tables; Persons, Cars and PersonCars. I assume you > > understand this pretty straight-forward notation: > > Persons: PersonId, Name, Phone > > Cars: CarId, Registration > > PersonCars: fkPersonId, fkCarId > > > > Now, I'd like to return a recordset containing each individual Person and > > registration numbers of the cars that that person owns. > > > > For example: > > By using a standard joined query I can get the following results: > > Name; Phone; Registration > > John Smith; 5551234; ABC123 > > John Smith; 5551234; BCD234 > > John Smith; 5551234; GHI665 > > > > But what I really want is this: > > Name; Phone; Registrations > > John Smith; 5551234; ABC123, BCD234, GHI665 > > > > Is this at all possible? Should I use a SP, a view or some kind of > > user-defined function? Or should I rather process this in code by querying > > separately for all the person's car, while iterating over each row in the > > recordset? > > > > Thanks a lot in advance, > > Christopher Carlander > > > > > > > Actually I was just thinking in terms of encapsulation. All calls to You're going to have to weigh encapsulation vs. performance, and a "new > retrieve this recordset should always be formatted in the same way, > regardless of presentation platform (be it PHP or ASP.NET). > > But of course it can be solved on the client side instead. I'm just keen > to > learn new approaches. :) approach" is not necessarily a better approach, nor even a good approach. Of course there are kludges that let you do this kind of thing in the database, but you're going to pay the price for it -- sure, your GUI code is a little simpler, but you've just thrown all set theory out the window and forced the database to use a cursor of sorts. But by all means, see http://www.aspfaq.com/2529 for a couple of ideas. The absolute best performing query is going to be the join you already have. The only place where you HAVE to loop through the rows row by row is on the client. So why not build a user control in .NET that does the concatenation for you and keep the CPU cycles on the database server happy. A I'm convinced, I won't spend more time on making the server perform stuff
it's not really meant to do. Thanks for the discussion. /Chris ### Show quote >> Actually I was just thinking in terms of encapsulation. All calls to >> retrieve this recordset should always be formatted in the same way, >> regardless of presentation platform (be it PHP or ASP.NET). >> >> But of course it can be solved on the client side instead. I'm just keen >> to >> learn new approaches. :) > > You're going to have to weigh encapsulation vs. performance, and a "new > approach" is not necessarily a better approach, nor even a good approach. > Of course there are kludges that let you do this kind of thing in the > database, but you're going to pay the price for it -- sure, your GUI code > is a little simpler, but you've just thrown all set theory out the window > and forced the database to use a cursor of sorts. But by all means, see > http://www.aspfaq.com/2529 for a couple of ideas. > > The absolute best performing query is going to be the join you already > have. The only place where you HAVE to loop through the rows row by row is > on the client. So why not build a user control in .NET that does the > concatenation for you and keep the CPU cycles on the database server > happy. > > A >  |
|||||||||||||||||||||||
Other interesting topics