Re: Merging results

From: Chuck Hamilton <chuck_hamilton_at_yahoo.com>
Date: Wed, 28 Jun 2000 10:18:58 -0700 (PDT)
Message-Id: <10542.110686@fatcity.com>

--0-1804289383-962212738=:4081
Content-Type: text/plain; charset=us-ascii

There's no way I know of in SQL to concatenate 100,000 result rows into 1 column. Probably not in unix either as I'm sure it would exceed the maximum line length.

I'm not so sure that's a good thing to even try. Why is it more efficient to concatenate the results into one result field? Is it something to do with the client? What's client? If it's something link VB or Powerbuilder maybe you could code the fetch routine to do the concatenation.

Andreas Jung <ajung_at_sz-sb.de> wrote:
On Tue, Jun 27, 2000 at 11:12:59AM -0700, Chuck Hamilton wrote:
>
> I'm not sure what you mean. If you mean you want to append a | to all rows the answer is simple...
>
> select docnum||'|' from ...;
>
> If you mean you want a new column for each row returned there is no way to do it in SQL without first knowing the number of rows being converted into columns. I think something like that would best be handled with a spreadsheet. You could probably do it in Unix too by piping the results of the query through awk and/or sed to build in "insert" or "create table as select... from dual" statement.

The user makes a search and gets a list document numbers as result. I need to store all document numbers from the query. When the user gets 100000 hits it is more efficient to store ale document numbers as one string in one column instead of storing in 100000 rows in a result table.. thats's the background.

Andreas

Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere! --0-1804289383-962212738=:4081
Content-Type: text/html; charset=us-ascii

<P>There's no way I know of in SQL to concatenate 100,000 result rows into 1 column. Probably not in unix either as I'm sure it would exceed the maximum line length.</P>
<P>I'm not so sure that's a good thing to even try. Why is it more efficient to concatenate the results into one result field? Is it something to do with the client? What's client? If it's something link VB or Powerbuilder maybe you could code the fetch routine to do the concatenation.<BR></P>
<P>&nbsp; <B><I>Andreas Jung &lt;ajung_at_sz-sb.de&gt;</I></B> wrote: <BR>
<BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px">On Tue, Jun 27, 2000 at 11:12:59AM -0700, Chuck Hamilton wrote:<BR>&gt; <BR>&gt; I'm not sure what you mean. If you mean you want to append a | to all rows the answer is simple...<BR>&gt; <BR>&gt; select docnum||'|' from ...;<BR>&gt; <BR>&gt; If you mean you want a new column for each row returned there is no way to do it in SQL without first knowing the number of rows being converted into columns. I think something like that would best be handled with a spreadsheet. You could probably do it in Unix too by piping the results of the query through awk and/or sed to build in "insert" or "create table as select... from dual" statement.<BR><BR>The user makes a search and gets a list document numbers as result.<BR>I need to store all document numbers from the query. When the user<BR>gets 100000 hits it is more efficient to store ale document numbers<BR>as one string in one column instead of stori!

ng in 100000 rows in a result table.. thats's the background. Andreas</BLOCKQUOTE> <hr size=1>Do You Yahoo!? Get Yahoo! Mail - Free email you can access from anywhere! Received on Wed Jun 28 2000 - 12:18:58 CDT