SQL statement collection

1. Column and column Conversion
Assume that there is a student summary table (CJ) as follows:
Name subject result
Zhang San Chinese 80
Zhang San, mathematics 90
Zhang San physical 85
Li Si Chinese 85
Li Si mathematics 92
Li Si physical 82

Want to become
Name, Chinese, Mathematics, Physics
Zhang San 80 90 85
Li Si 85 92 82

The solution is as follows:
Declare @ 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
Select @ SQL = @ SQL + 'from test group by name'
Exec (@ SQL)

In addition, transform is provided in access to implement row-column conversion.
Transform count (result) as number
Select name
From student orders table
Group by name
Reply subject;

The usage of transform is as follows:
========================================================== ========================
Transform aggfunction
Selectstatement
Struct tfield [IN (value1 [, value2 [,...])]

The transform statement can be divided into the following parts:

Partial description
The SQL aggregate function that aggfunction operates on the selected data.
Select statement.
The field or expression used to create a column title in the query result set.
Value1 and value2 are used to create a fixed value for the column title.

Description
When you use cross tabulation queries to abstract data, select a value from the specified field or expression as the column title,
In this way, you can observe the data in a more compact format than the selected query.
Transform is optional, but it must be used as the first statement in the SQL string.
It appears before the SELECT statement (the field specified as the row title) and the group by clause
Before (specify the row group. Can selectively include other clauses, such as the WHERE clause, which specifies
Select or sort conditions. You can also use a subquery as a predicate, especially in the WHERE clause of a cross-Table query.

The value returned by effectfield is used as the column title in the query result set.
For example, in a cross tabulation query, 12 columns are created based on the sales chart by month.
You can restrict the use of a fixed value (value1, value2) in the optional in Clause in the field to create a title.
You can also use a fixed value without data to create an additional column.
========================================================== ========================================================== =

2. Column-based conversion
Temporarily retained

3. Column and column conversion-add and merge
Table,
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
Create Function fmerg (@ id int)
Returns varchar (8000)
As
Begin
Declare @ STR varchar (8000)
Set @ STR =''
Select @ STR = @ STR + ',' + Cast (PID as varchar) from Table A where id = @ ID
Set @ STR = right (@ STR, Len (@ Str)-1)
Return (@ Str)
End
Go

-- Call the custom function to obtain the result.
Select distinct ID, DBO. fmerg (ID) from Table

4. How to obtain all column names of a data table
The method is as follows: first obtain the systemid of the data table from the systemobject system table, and then retrieve all the column names of the data table from the syscolumn table.
The 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 order by colid

Is it too simple? Haha, but it is often used.

5. Use SQL statements to change the User Password

SysAdmin role is required to modify other users.
Exec sp_password null, 'newpassword', 'user'

If the account is Sa, execute exec sp_password null, 'newpassword', SA

6. How can I determine which fields in a table cannot be blank?

Select column_name from information_schema.columns where is_nullable = 'no' and table_name = tablename

7. How can I find a table with the same fields in the database?
A. query the names of known Columns
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. query all the names of unknown columns 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
)
8. query data of row xxx

Assume that 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 = BB. ID)
You can also use a cursor.
Fetch absolute [number] from [cursor_name]
The number of rows is absolute.

9. SQL Server date calculation
A. the first day of a month
Select dateadd (mm, datediff (mm, 0, getdate (), 0)
B. Monday of the week
Select dateadd (wk, datediff (wk, 0, getdate (), 0)
C. The first day of a year
Select dateadd (YY, datediff (YY, 0, getdate (), 0)
D. The 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 (MS,-3, dateadd (YY, datediff (YY, 0, getdate (), 0 ))
G. Last day of the month
Select dateadd (MS,-3, dateadd (mm, datediff (M, 0, getdate () + 1, 0 ))
H. The first Monday of this month
Select dateadd (wk, datediff (wk, 0,
Dateadd (DD, 6-datepart (day, getdate (), getdate ())
), 0)
I. The last day of the year
Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate () + 1, 0 )).