A friend once asked me the following question: how to add a new column (regular) in the query results )?
For example, the structure and data in the database are as follows: (tablename: People)
Name age
Changchang 20
Charles 23
Sunny 21
Huangzhs 22
Dazu 65
Now, the query results are displayed as follows using SQL statements:
ID name age
1 changchang 20
2 Charles 23
3 sunny 21
4 huangzhs 22
5 Dazu 65
First, analyze this question: (dynamically Add a new column (incrementing ))
Add a new column in the query result set. The Select * from people column does not contain the ID column.
Therefore, we need to add in the query result set (one row is added ).
Technologies involved in implementation:
The first thing that comes to mind is to use a cursor (the cursor can traverse every record in the result set ).
In addition, we can create a temporary table or table variable to store the data after the new column is added.
Finally, you can query the temporary table to include a new column in the query result.
The preceding results are achieved through SQL statements:
-- Define parameters
Declare @ I int
Declare @ name varchar (50)
Declare @ age int
-- Declare a cursor
Declare c_people cursor
For select * from people
-- Open the cursor
Open c_people
-- Create a temporary table
Create Table # mypeople
(ID int,
Name varchar (50 ),
Age INT)
Set @ I = 1
-- Read data
Fetch next from c_people into @ name, @ age
While @ fetch_status = 0
Begin
-- Dynamically Add a new column to the temporary table
Insert into # mypeople
Values (@ I, @ name, @ age)
Set @ I = @ I + 1
Fetch next from c_people into @ name, @ age
End
-- Close the cursor
Close c_people
-- Delete a cursor
Deallocate c_people
-- Query temporary tables
Select * from # mypeople
-- Delete a temporary table
Drop table # mypeople
The above is the implementation method I have come up. Of course it seems complicated. If there is any simple solution. I hope that my friends can make suggestions in a timely manner. Sharing knowledge is my greatest pleasure.
