Classic SQL FAQ Collection

On the internet often turn, often see some people in order to get some SQL statements and the burn, now I particularly own collection of some of the more refined code of SQL out and share with you

1. Row and column conversion--ordinary

Suppose there is a Student score table (CJ) as follows name Subject result John Language 80 three Mathematics 90 three physics 85 Li four languages 85 Dick Mathematics 92 Dick Physics 82

Want to be the name of the Chinese mathematical physics Zhang 380 90 85 Li 485 92 82

DECLARE @sql varchar (4000) Set @sql = ' Select Name ' Select @sql = @sql + ', sum (case Subject when ' +subject+ "' then result [' +subject+ '] ' from (select distinct Subject to CJ) as aselect @sql = @sql + ' from test group by name ' EXEC (@sql)

2. Row and column conversions-merging

Table A, ID PID 1 1 1 2 1 3 2 1 2 2 3 1 How to convert table b:id PID 1 1,2,3 2 1,2 3 1

Create a merged function creation function Fmerg (@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.fmerg (ID) from Table A

3. How to get all the column names of a data table

The method is as follows: Get the systemid of the datasheet from the SystemObject system table, and then syscolumn the table with all the column names of the datasheet. The SQL statement is as follows: Declare @objid int, @objname char set @objname = ' tablename ' Select @objid = ID from sysobjects where id = object _id (@objname) Select ' column_name ' = name from syscolumns where id = @objid ORDER by colid

Is it too easy? Oh, but often use AH.

4. Change the user's password through SQL statements

To modify someone else's, need sysadmin role EXEC sp_password NULL, ' newpassword ', ' User '

If the account number executes exec sp_password NULL for SA, ' newpassword ', SA

5. How can you tell which fields in a table are not allowed to be empty?

Select column_name from INFORMATION_SCHEMA. COLUMNS where is_nullable= ' NO ' and table_name=tablename

6. How do I find a table in the database that contains the same fields?   A. Checking known column names select B.name as tablename,a.name as ColumnName from syscolumns a INNER JOIN sysobjects B on A.id=b.id and B.type= ' u ' and a.name= ' your field name '

B. Unknown column name check all column names that appear in different tables 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 syscolumns S2 Where s1.name = S2.name and s1.id <> s2.id)

7. Query XXX line data

Suppose the ID is a primary key: SELECT * FROM (select top XXX * from yourtable) AA where NOT EXISTS (select 1 from (select Top X Xx-1 * from yourtable) BB where aa.id=bb.id) if the cursor is also available, fetch absolute [number] from [cursor_name] rows are absolute rows

8. SQL Server Date calculation a. First day of one months select  DATEADD (mm,  DATEDIFF (Mm,0,getdate ()),  0)   B. This week in Monday select  DATEADD (wk,   DATEDIFF (Wk,0,getdate ()),  0) c. First day of the year select  DATEADD (yy,  DATEDIFF (Yy,0,getdate ()),  0) & nbsp D. First day of the quarter select  DATEADD (qq,  DATEDIFF (Qq,0,getdate ()),  0)   E. Last day of last month select  DATEADD (ms,- 3,dateadd (mm,  DATEDIFF (Mm,0,getdate ()),  0))   F. Last day of last year select  DATEADD (yy,   DATEDIFF (Yy,0,getdate ()),  0) G. The last day of this month select  DateAdd (Ms,-3,dateadd mm,  (DATEDIFF, GETDATE ()) +1,  0)   H. This month's first Monday select  DATEADD (wk,  DATEDIFF                                                                                         DateAdd (dd,6- DatePart (Day,getdate ()), GETDATE ())                                                                                                               ),  0       I. The last day of this year select  DateAdd (ms,-3,dateadd yy,  DATEDIFF (yy, 0,getdate ()) +1,&nbsP 0)).



Thanks to the authors who provide relevant SQL online