Re: SQL Question

Ng K C Paul wrote:
>
> I want the result can be displayed by any front end report writer.
>
> If I set the pagesize to 10, it will waste a lot of paper.
>
> David Greensmith (david_at_green1.demon.co.uk) wrote:
> : How about setting pagesize to 10?
> :
> : David
> :
> : 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?
> :
> : David Greensmith :-)
> : (david_at_green1.demon.co.uk)

Bear with me on this one ... try this:

This will require a couple of assumptions. All the columns you want to display are character based (char / varchar), or can be converted to characters for reporting (i.e., you can convert the numbers/dates to characters). Alternatively, you can change Null values to display spaces (blanks) and not NULL when there is a null value in the returrned result set.

This is how I "solve" it:

/*
Select all the data you want to report into a temporary table, adding an identity column, and ordering it in the required sort order of the report.
*/

select id = Identity(5), *
into #temp
from source_table

/*
Select the identity_column (adding a decimal place to it at the same time) and all the report columns (converting them to character based values) into a second temp table.

Union this select with a query which inserts one record for every 10th record in the first temp table. This is calculated by the modulo on the identity column. i.e. if the identity column id id equivalent to the identity column minus the remainder when divided by 10 e.g. IS 15 = 15 - (15 % 10)? becomes is 15 = 15 - 5 which becomes is 15 = 10 which is false, but for every tenth record 10, 20, 30, etc. this is true.

Thus we insert one record which is one decimal place greater than the identity value, i.e. 10 becomes 10.1. We also select spaces for every column required in the report.
*/

select Convert (numeric (6,1), id) id, convert (varchar (10), profile_id) profile_id
into #temp2
from #temp
union
select id + 0.1, ''
from #temp where id = id - (Convert (integer, id) % 10)

/*
Finally, we select the reporting columns only from the second temp table ordered by the id column with the decimal place. */

select profile_id from #temp2
order by id

Done.

This gives one blank line after every 10th record.

gus. Received on Tue Sep 29 1998 - 00:00:00 CDT