Merge IDs with the same SQL statement

Create table deom

(

Id int primary key identity (1, 1 ),

Code varchar (32 ),

Txt varchar (64)

)

Insert into deom (code, txt)

Select '200', 'scientific 'union

Select '200', 'mate' union

Select '200', 'math 1' union

Select '200', 'artbe' union

Select '200', 'Sports 'union

Select '123', 'Sports 1'

Create function s_str (@ code varchar (20 ))

Returns varchar (100)

Begin

Declare @ a varchar (1000)

Set @ a =''

Select @ a = @ a + ',' + txt from deom where code = @ code

Set @ a = stuff (@ a, 1,1 ,'')

Return @

End

Go

Select code, name = dbo. s_str (code) from deom group by code

Effect:

Use a # number before the table name. The temporary table is local and uses two # numbers. The temporary table is global. After the connection is disconnected, the SQL statement automatically deletes the temporary table.

Create table #

(

Id int,

Name varchar (50)

)

Insert into # a (id, name) values (1, '20140901 ')

Select * from #

Drop table #

In addition to the # number before the name, the temporary table has the same operations as the normal table.

Tb_Student is a created table. We can copy the content in the tb_Student table to the tb_lizi table through the temporary table temp, which can be implemented using the following code:

Use OLAP

SELECT * INTO # temp FROM tb_Student

SELECT * INTO tb_lizi FROM # temp

After the execution, disconnect the SQL connection and reconnect (you can also exit the sq and then restart the SQL). It is found that the content in the tb_Student table in the tb_lizi table is completely consistent, and the replication is realized, at the same time, we didn't use code to delete the temp table, but there was no temp table in the apsaradb for MySQL database. This is because the SQL automatically deletes the temp table when the connection is disconnected.