Classic SQL FAQ Collection

Source: Internet
Author: User
Tags getdate sql

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 as tablename, as ColumnName from syscolumns a INNER JOIN sysobjects B on and B.type= ' u ' and ' your field name '

B. Unknown column name check all column names that appear in different tables select as tablename, as ColumnName from syscolumns s1, sysobjects o Where s1  . id = and o.type = ' U ' and Exists (Select 1 from syscolumns S2 Where = and <>

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 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.