Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Question
In article <6ufghe$otc$1_at_imsp009a.netvigator.com>, paulkcng_at_news.netvigator.com (Ng K C Paul) wrote:
> I have a table with 100 records.
>
> I want my report to retrieve all records from this table and display a blank
> line for every ten records listed out. Is it possible using SQL?
There are many solutions to this -- especially if it were in a stored procedure.
But I'll assume you mean a single select statement?
One way would be to copy your table into a temp table with an identity
column, then issue the select from that temp table and applying a linefeed
(char(10)
every 10 lines (using modulus to determine if row number is divisible by 10).
Here's a simulation, but using 2 instead of 10 as the breakpoint for ease of illustration.
CREATE TABLE #sample1(row_num tinyint,
name char(10)) INSERT #sample1 values(1,'Andy') INSERT #sample1 values(2,'Barney') INSERT #sample1 values(3,'Otis') INSERT #sample1 values(4,'Goober') INSERT #sample1 values(5,'Bea') INSERT #sample1 values(6,'Opie') INSERT #sample1 values(7,'Floyd') INSERT #sample1 values(8,'Ernest')
SELECT convert(char(20),name + replicate(char(10),1-sign(row_num%2))) FROM #sample1
GO
Otis
Goober
Bea
Opie
Floyd
Ernest
Thelma
Juanita
(10 rows affected)
Can you do it without the temp table step? Yes, but it would require a self join with grouping and would be slower performing. Received on Fri Sep 25 1998 - 00:00:00 CDT