Re: SQL Question

From: Rick Garster <garster_at_earthlink.net>
Date: 1998/09/25
Message-ID: <garster-2509980946260001@144.163.245.67>#1/1

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')

INSERT #sample1 values(9,'Thelma')
INSERT #sample1 values(10,'Juanita')

SELECT convert(char(20),name + replicate(char(10),1-sign(row_num%2))) FROM #sample1

GO

---

 Andy
 Barney

 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