Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-16 : 23:08:53
|
Hyung writes "Hi,
I’m trying to create stored procedure as following.
1. First, I created 2 tables
Create Table People (
PID smallint (2) identity (1,1),
FName varchar (30),
LName varchar (30))
Go
Create Table Mony (
MonyID smallint (2) identity (1,1),
PID smallint (2),
Amount money (8),
[Date] datetime (8))
Go
2. Then, I run the query for intermediate table.
SELECT PID, FName + ' ' + LName AS Name, Amount
FROM People p, Mony m
WHERE p.PID = m.PID
ORDER BY PID
PID Name Amount
--------------------------------
1 James Don 100
2 Paul Smith 220
2 Paul Smith 20
2 Paul Smith 30
3 John Doe 400
3. From this table, I want to create another new table like this:
PID Name Amount1 Amount2 Amount 3 TotalAmount
-----------------------------------------------------------------
1 James Don 100 100
2 Paul Smith 220 20 30 270
3 John Doe 400 400
So, my question is:
Is there any way I can use the particular columns values (i.e. Amount) as the new table's select lists (Amount1, 2, 3…) dynamically in the new table?
The number of new columns is not fixed.
I read a book about stored procedure and it says:
- It is not possible to collect information if a stored procedure returns more than one resultset.
- It is (still) not possible to use a table variable as the output parameter of a stored procedure (for table datatype in the SQL server 2000)
Do I have to use a cursor as an output parameter?
Or temporary table?
I'm currently using the sql server 2000 (beta 2), but this stored procedure will be used in the sql server 7.0.
Thank you." |
|
|
|
|
|
|
|
SP for dynamic summary table