Adding an extra row -- sql server programming

Joe Celko
11/24/2004 8:39:48 PM

[quoted text, click to view]
procedure. The parameterised stored procedure returns varying numbers
of columns. The number of columns varies by the number of months which
the report covers. <<

You missed the entire point of SQL. It is a data retrieval langauge
which returns tables. Tables are models of entities or relationships
and by definition have a fixed, known set of columns.

[quoted text, click to view]

A set is made up of the same kind of things; this is a report, not a
set.

[quoted text, click to view]
Products could be named anything up to 'Z', how could I do this? <<

By using a report writer to write a report. I am sure there is some
kludge you can write trying to use in T-SQL as an application
development language. But if you are going to do that, it would be
better just to go back to file systems and 3GL programming.

It is also very hard to debug your code or DDL when you don't post any.

--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

*** Sent via Developersdex http://www.developersdex.com ***

Huiyong Lau
11/24/2004 9:09:08 PM

"With rollup" can do what you want. Listed below is a sample code kinda
related to your previous example.

select id, isnull(description,'Total:') 'description', sum(cola) 'x',
sum(colb) 'y', sum(colc) 'z'
from tbl
group by id, description with rollup
having (id is not null and description is not null) or (id is null and
description is null)

HTH,
Huiyong

[quoted text, click to view]

Huiyong Lau
11/24/2004 9:39:02 PM

Forgot you need sorting over description column. The previous code should
works for descending order. Here's a modified version for ascending order.
Replace 256 with the description column length to ensure the "Total" will
always listed at the end.

select id, isnull(description,'Total:') 'description', sum(cola) 'x',
sum(colb) 'y', sum(colc) 'z'
from tbl
group by id, description with rollup
having (id is not null and description is not null) or (id is null and
description is null)
order by isnull(description, replicate('Z',256)) asc

Sorry for the missed.

Cheers,
Huiyong
[quoted text, click to view]

Kath
11/24/2004 11:04:18 PM

Hi everyone,

I have a parameterised stored procedure which calls another stored =
procedure.

The parameterised stored procedure returns varying numbers of columns. =
The number of columns varies by the number of months which the report =
covers.

For example:

Covering Jan to March would return:

ID Description 2004_01 2004_02 2004_03

1 Product 1 100.83 120.64 130.25
2 Product 2 88.74 96.33 204.50

I want to add a Total line to the foot of this recordset.

Now, bearing in mind the report is ordered by Description, and the =
Products could be named anything up to 'Z', how could I do this?

Suggestions very welcome!!

Thanks

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Adding an extra row