Hi,
I'n in an environment where I cannot make stored procedures. Now I need
to make a query with a subquery in the SELECT part which gives a comma
separated list of results:
SELECT
p.id,
listFunction(SE LECT name FROM names WHERE name_parent=p.i d) AS
'nameList'
FROM projects AS p
This query should return something like:
1, "john,mike,petr a"
2, "bob,carl,sandr a,peter,
etc
listFunction is (of course) not (yet) defined. Is this possible without
the use of stored procedures?
Mike 12 29079
insomniux (mi************ *@hccnet.nl) writes: I'n in an environment where I cannot make stored procedures. Now I need to make a query with a subquery in the SELECT part which gives a comma separated list of results:
SELECT p.id, listFunction(SE LECT name FROM names WHERE name_parent=p.i d) AS 'nameList' FROM projects AS p
This query should return something like: 1, "john,mike,petr a" 2, "bob,carl,sandr a,peter, etc
listFunction is (of course) not (yet) defined. Is this possible without the use of stored procedures?
In SQL 2000, it is actually not possible even with stored procedures
yo write such a function. You will have to write an iterative
solution that iterates over the table, and which uses a temp table
to aggregate the columns.
In SQL2005 you can do this in a single query, thanks to some of the
new XML stuff in SQL 2005.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
I know there are various aggregate functions for numeric columns (like
min, max, ...). Wouldn't it be possible to write an aggregate function
for string-type columns which would enable to write the query as:
SELECT
p.id,
(SELECT listFunction(na me) FROM names WHERE name_parent=p.i d GROUP
BY name_parent) AS
'nameList'
FROM projects AS p
Erland Sommarskog wrote: insomniux (mi************ *@hccnet.nl) writes: I'n in an environment where I cannot make stored procedures. Now I
need to make a query with a subquery in the SELECT part which gives a
comma separated list of results:
SELECT p.id, listFunction(SE LECT name FROM names WHERE name_parent=p.i d) AS 'nameList' FROM projects AS p
This query should return something like: 1, "john,mike,petr a" 2, "bob,carl,sandr a,peter, etc
listFunction is (of course) not (yet) defined. Is this possible
without the use of stored procedures?
In SQL 2000, it is actually not possible even with stored procedures yo write such a function. You will have to write an iterative solution that iterates over the table, and which uses a temp table to aggregate the columns.
In SQL2005 you can do this in a single query, thanks to some of the new XML stuff in SQL 2005.
-- Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Hi
If this is for a client to display the information, then the best place to
do this would be on the client itself.
John
"insomniux" <mi************ *@hccnet.nl> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. . Hi, I'n in an environment where I cannot make stored procedures. Now I need to make a query with a subquery in the SELECT part which gives a comma separated list of results:
SELECT p.id, listFunction(SE LECT name FROM names WHERE name_parent=p.i d) AS 'nameList' FROM projects AS p
This query should return something like: 1, "john,mike,petr a" 2, "bob,carl,sandr a,peter, etc
listFunction is (of course) not (yet) defined. Is this possible without the use of stored procedures? Mike
I have access to a webinterface which I can feed with SQL, but some
statements are blocked.
I would prefer to create the statement in pure SQL but I think this is
not possible. I know how to get the first and the last value (using top
1 and sorting), but most subqueries return more than 3 rows. I know it
is possible to put all these values in separate columns, but the SQL
statements will become somewhat terrible.
insomniux wrote: Hi, I'n in an environment where I cannot make stored procedures. Now I need to make a query with a subquery in the SELECT part which gives a comma separated list of results:
SELECT p.id, listFunction(SE LECT name FROM names WHERE name_parent=p.i d) AS 'nameList' FROM projects AS p
This query should return something like: 1, "john,mike,petr a" 2, "bob,carl,sandr a,peter, etc
listFunction is (of course) not (yet) defined. Is this possible without the use of stored procedures? Mike
This *should* be very easy to do in your client, looking up the appropriate
functions may be much faster than trying to force it into SQL. The precise
steps you want to be able to execute are (in pseudocode):
// step 1, execute the query in whatever syntax your
// client language uses
//
$some_query = query_command(" select ....")
// step 2, pluck out the values of one column to an array,
// your client should have some function for this
//
$some_array = extract_column( $some_query,"Co lumnA");
// step 3, in PHP the function is "implode", you want a function
// that collapses an array into a delimited string
//
$some_list = implode(",",$so me_array);
Best of luck, HTH.
--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec )ure(Dat)a(.com )
insomniux (mi************ *@hccnet.nl) writes: I know there are various aggregate functions for numeric columns (like min, max, ...). Wouldn't it be possible to write an aggregate function for string-type columns which would enable to write the query as:
No, you cannot write your own aggregate functions in SQL 2000.
In SQL 2005 you can - using a CLR language. However, as I understand it,
it does not work very well for this case, at least not if you want the
lists to be ordered.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Kenneth Downs wrote in message insomniux wrote:
Hi, I'n in an environment where I cannot make stored procedures. Now I need to make a query with a subquery in the SELECT part which gives a comma separated list of results:
SELECT p.id, listFunction(SE LECT name FROM names WHERE name_parent=p.i d) AS 'nameList' FROM projects AS p
This query should return something like: 1, "john,mike,petr a" 2, "bob,carl,sandr a,peter, etc
listFunction is (of course) not (yet) defined. Is this possible without the use of stored procedures? Mike
This *should* be very easy to do in your client, looking up the appropriate functions may be much faster than trying to force it into SQL. The precise steps you want to be able to execute are (in pseudocode):
// step 1, execute the query in whatever syntax your // client language uses // $some_query = query_command(" select ....")
// step 2, pluck out the values of one column to an array, // your client should have some function for this // $some_array = extract_column( $some_query,"Co lumnA");
// step 3, in PHP the function is "implode", you want a function // that collapses an array into a delimited string // $some_list = implode(",",$so me_array);
Best of luck, HTH.
Unfortunately my client application does not allow me to use this kind
of syntax. Only SQL.
Thanks
"insomniux" <mi************ *@hccnet.nl> wrote in message news:11******** **************@ g14g2000cwa.goo glegroups.com.. . I know there are various aggregate functions for numeric columns (like min, max, ...). Wouldn't it be possible to write an aggregate function for string-type columns which would enable to write the query as:
SELECT p.id, (SELECT listFunction(na me) FROM names WHERE name_parent=p.i d GROUP BY name_parent) AS 'nameList' FROM projects AS p
Yes, it would. The engineers at iAnywhere (SQL Anywhere) figured this one out nearly a decade ago...
SELECT list( [distinct] expression )
FROM tablename
is their aggregate on strings. You can even order the list (in addition to ordering the entire result set), and specify
custom delimiter characters (if you don't like commas). www.ianywhere.com
-Paul Horan-
Sr. Architect
VCI Springfield, Mass
"insomniux" <mi************ *@hccnet.nl> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. . Hi, I'n in an environment where I cannot make stored procedures. Now I need to make a query with a subquery in the SELECT part which gives a comma separated list of results:
SELECT p.id, listFunction(SE LECT name FROM names WHERE name_parent=p.i d) AS 'nameList' FROM projects AS p
This query should return something like: 1, "john,mike,petr a" 2, "bob,carl,sandr a,peter, etc
listFunction is (of course) not (yet) defined. Is this possible without the use of stored procedures? Mike
This is a function I use to do what you describe. I found the function
someplace on the Internet (don't remember where). Maybe you can modify it
for your use.
DECLARE @NbrList VarChar(300)
Declare @InvoiceID Varchar(30)
SELECT @NbrList = COALESCE(@NbrLi st + ', ', '') +
CAST(C.TRACKING _NO AS varchar(30))
FROM dbo.SHIPPER S INNER JOIN
dbo.SHIPPER_LIN K SL ON S.PACKLIST_ID = SL.PACKLIST_ID
INNER JOIN
dbo.CARTON_LINE CL ON SL.CARTON_ID = CL.CARTON_ID AND
SL.CARTON_LINE_ NO = CL.LINE_NO INNER JOIN
dbo.CARTON C ON CL.CARTON_ID = C.ID
WHERE S.INVOICE_ID = @InvoiceID
SELECT @NbrList
GO
Kevin This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Lin Ma |
last post by:
Greetings,
In my search application, user can type a number to search. I use LIKE in my
query.
If a query result generates over 10,000 recordsets, it may several minutes
to run.
Is there a way to only query certain recordset at a time?
|
by: Surajit Laha |
last post by:
I am firing a query like:
SELECT TaskName, StartDate FROMTasks
WHERE StartDate >= '01-Aug-2003'
Now the result comes as:
TaskName StartDate
--------------------------
Task1 01-Aug-2003
|
by: Jack |
last post by:
Hi,
I have a form when loaded, retrieves record from an access table. Among
other fields there is a check box called FinalUpdate. This is tied to a field
in Access of type Yes/No.
The form retieves the values perfectly. This form is being used to update
the record in the table via a successconfirmation.asp.
Now, when the checkbox is loaded as checked, then unchecking the checkbox
reflects the change in query result in...
|
by: Wei Wang |
last post by:
Hi,
I want to do a select in dynamic command, something like:
TRIGGER FUNCTION
DECLARE
table_name_suffix text;
temp_result RECORD;
temp_result2 RECORD;
|
by: Martin Sarsale |
last post by:
Dear All:
Im looking for solutions (Free Software is better) to do query result
caching.
Thanks to the people from #postgresql I know that postgres doesn't do
that by himself and the solution should be some kind of middle ware.
This is our problem: we are executing the same set of SELECT SP/queries
hundreds of times, without updating the DB. We would like to cache the
result of this SP/queries so the next time somebody runs one of...
| |
by: RookieDan |
last post by:
Greetings fellow Accessers!
Im new but in Access, but I have some background in different coding.
I have a programme loading customer data into Access belonging to BMW dealers in Europe.
Every dealer reports several customers and I have today a query that sorts out how many customer data each BMW dealer sends in to us. The query is also referring to a startdate and enddate (to be filled in in a messagebox) so that i can choose time...
|
by: Maarten van der Cammen |
last post by:
Hi,
In a table I have two numeric fields (e.g. FieldA and FieldB). Both fields
have double precicion (field size "double"), Format "Fixed" and Decimal
places "3" since I want to calculate with at least 3 digits precision (e.g.
values might show as 0.123).
In a select query, I divide FieldA by FieldB. All results show properly.
Then I want to display all records that show values above 0.5. To do so, I
|
by: arial |
last post by:
Hi,
I am having trouble checking a sql query result = null in c#.
my query is like,
sqlcommand cmd.commandText = "select cqinum from table1";
how can cmpare this result to null?
|
by: CCHDGeek |
last post by:
How can I tell if a query result empty (ie there are no records with the specified criteria). I want to change a form's design based on the result of the query it is based how. Does anyone know how I'd go about doing that?
Thanks
|
by: ddtpmyra |
last post by:
how can I capture the query result in PHP?
I have two queries below:
# Fetch the file information
$query ="update filestorage set approved ='Y' where FileID = {$id}";
$query1 ="select members.email from members, filestorage where filestorage.author = members.username and FileID = {$id}" ;
then execute the query using command below:
$result = @mysql_query($query) or die("Error! Query failed: <pre>". mysql_error($dbLink)...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |