1. Layer conversion - ordinary assumptions include Zhang Xuecheng's transcript (CJ) Name Subject Result Zhang Sanyang 80 sheets three mathematics 90 sheets three physics 85 Li Si language 85 Li Fudi Mathematics 92 Li Si physics 82 want to become a name Chinese mathematics Physical Zhang San 80 90 85 Li Si 85 92 82Declare @SQL VARCHAR (4000) SET @SQL = 'SELECT NAME'SELECT @SQL = @SQL ', SUM (Case Subject when '' Subject '' 'Then Result End [' Subject '] 'from (Select Distinct Subject from CJ) AS ASELECT @SQL = @ SQL ' from test group by name'exec (@SQL) 2. Ramature conversion - combined with table A, ID PID 1 1 1 2 1 3 2 1 2 2 3 1 How to make a table B: ID PID 1 1, 2, 3 2 1, 2 3 1 Create a merged function crete Ferg (@ID int) returns varchar (8000) asbegindeclare @str VARCHAR (8000) set @str = '' SELECT @ Str = @ Str ',' Cast (PID as varchar) from table a where id = @ idset @ Str = Right (@ Str, Len (@STR) -1) Return (@str) endgo - Call the custom function to get the result Select Distinct ID, DBO.fmerge (ID) from the FROM table A3. How to get all the column names of a data table is as follows: First get the data table from the SystemObject system table SYSTEMID, then then all column names of the data table in the syscolumn table.
SQL statement is as follows: declare @objid int, @ objname char (40) set @objname = 'tablename'select @objid = id from sysobjects where id = object_id (@objname) select' Column_name '= name from syscolumns where id = @objid Is it too simple to order by colid? Oh, I often use A.4. Change the user's password to modify others through the SQL statement, need sysadmin role exec sp_password null, 'newpassword', 'user' If the account is SA executes Exec sp_password null, 'newpassword', sa 5. How to determine which fields do not allow empty? Select column_name from information_schema.columns where is_null = 'no' and table_name = TABLENAME 6. How do I find a table with the same field in the database? a. Syrics known to the column name SELECT B.NAME AS TABLENAME, A.NAME As ColumnName from syscolumns a.id = B.ID and B.TYPE = 'u' and a.name = ' Your field name 'b. Unknown column names All column name Select O.Name as Tablename, S1.Name As ColumnName from syscolumns s1, sysobjects o where s1.id = o.id and o.type = 'U' and exists (SELECT 1 from s2.name and s1.name = s2.name and s1.id <> s2.id) 7. Query the XXX line data hypothesis the ID is the primary key: select * from (SELECT TOP XXX * From YourTable) aa where not exists (SELECT 1 from (SELECT TOP XXX-1 * from YourTable) bb Where aa.id =
