SQL and T-SQL Learning (1)

1. Column and column conversion-common

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

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)

2. Column and column conversion-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

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

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

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

6. 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
)

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

8. 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 )).

Create procedure cmrc_productsearch
(
@ Search nvarchar (1, 255)
)
As

Select
Productid,
Modelname,
Modelnumber,
Unitcost,
Productimage

From
Cmrc_products

Where
Modelnumber like '%' + @ search + '%'
Or
Modelname like '%' + @ search + '%'
Or
Description like '%' + @ search + '%'

Go