Simple and useful SQL scripts (transpose columns and querying the same records in a table)

Exchange of rows and columns
Copy codeThe Code is as follows:
Create table test (id int, name varchar (20), quarter int, profile int)
Insert into test values (1, 'A', 1,1000)
Insert into test values (1, 'A', 2,2000)
Insert into test values (1, 'A', 3,4000)
Insert into test values (1, 'A', 4,5000)
Insert into test values (2, 'B', 1,3000)
Insert into test values (2, 'B', 2,3500)
Insert into test values (2, 'B', 3,4200)
Insert into test values (2, 'B', 4,5500)
Select * from test
-- Row-to-column Conversion
Select id, name,
[1] as "first quarter ",
[2] as "Quarter 2 ",
[3] as "three quarters ",
[4] as "fourth quarter ",
[5] as "5"
From
Test
Bytes
(
Sum (profile)
For quarter in
([1], [2], [3], [4], [5])
)
As pvt

Create table test2 (id int, name varchar (20), Q1 int, Q2 int, Q3 int, Q4 int)
Insert into test2 values (1, 'A)
Insert into test2 values (2, 'B', 3000,3500, 4200,5500)
Select * from test2
-- Column-to-row
Select id, name, quarter, profile
From
Test2
Unregister
(
Profile
For quarter in
([Q1], [Q2], [Q3], [Q4])
)
As unpvt


SQL replacement string substring replace
Copy codeThe Code is as follows:
-- Example 1:
Update tbPersonalInfo set TrueName = replace (TrueName, substring (TrueName, 2, 4), '**') where ID = 1
-- Example 2:
Update tbPersonalInfo set Mobile = replace (Mobile, substring (Mobile,), '********') where ID = 1
-- Example 3:
Update tbPersonalInfo set Email = replace (Email, 'chinamobile ',' ******* ') where ID = 1

SQL queries having records of the same table
If an ID can be differentiated, you can write it like this.
Copy codeThe Code is as follows:
Select * from table where ID in (
Select ID from Table group by ID having sum (1)> 1 ))

If several IDS can be distinguished, you can write
Copy codeThe Code is as follows:
Select * from table where ID1 + ID2 + ID3 in
(Select ID1 + ID2 + ID3 from Table group by ID1, ID2, ID3 having sum (1)> 1 ))

Other answers: The data table is zy_bho. I want to find records with the same ZYH field name.
Copy codeThe Code is as follows:
-- Method 1:
SELECT * FROM zy_bho a WHERE EXISTS
(SELECT 1 FROM zy_bho WHERE [PK] <> a. [PK] and zyh = a. ZYH)
-- Method 2:
Select a. * from zy_bho a join zy_bho B
On (a. [pk] <> B. [pk] and a. zyh = B. zyh)
-- Method 3:
Select * from zy_bbo where zyh in
(Select zyh from zy_bbo group by zyh having count (zyh)> 1)
-- The primary key is a primary key or a unique field.

Converts multi-row SQL data into one multi-column data, that is, adding columns.
Copy codeThe Code is as follows:
Select
DeptName = O. OUName,
'9g' = Sum (Case When PersonalGrade = 9 Then 1 Else 0 End ),
'8g' = Sum (Case When PersonalGrade = 8 Then 1 Else 0 End ),
'7g4 '= Sum (Case When PersonalGrade = 7 AND JobGrade = 4 Then 1 Else 0 End ),
'7g3 '= Sum (Case When PersonalGrade = 7 AND JobGrade = 3 Then 1 Else 0 End ),
'6g' = Sum (Case When PersonalGrade = 6 Then 1 Else 0 End ),
'5g3 '= Sum (Case When PersonalGrade = 5 AND JobGrade = 3 Then 1 Else 0 End ),
'5g2 '= Sum (Case When PersonalGrade = 5 AND JobGrade = 2 Then 1 Else 0 End ),
'4g' = Sum (Case When PersonalGrade = 4 Then 1 Else 0 End ),
'3g2 '= Sum (Case When PersonalGrade = 3 AND JobGrade = 2 Then 1 Else 0 End ),
'3g1 '= Sum (Case When PersonalGrade = 3 AND JobGrade = 1 Then 1 Else 0 End ),
'2' = Sum (Case When PersonalGrade = 2 Then 1 Else 0 End ),
'1g '= Sum (Case When PersonalGrade = 1 Then 1 Else 0 End ),
-- 'Undefined level' = Sum (Case When PersonalGrade = NULL Then 1 Else 0 End)

Table Replication
Copy codeThe Code is as follows:
Insert into PhoneChange_Num ([IMSI], Num)
SELECT [IMSI]
, Count ([IMEI]) as num
FROM [Test]. [dbo]. [PhoneChange] group by [IMSI] order by num desc

Syntax 1: Insert INTO table (field1, field2,...) values (value1, value2 ,...)
Syntax 2: Insert into Table2 (field1, field2 ,...) select value1, value2 ,... from Table1 (the target table Table2 must exist. Because the target table Table2 already exists, We can insert constants in addition to the fields in the source table table1 .)
Syntax 3: SELECT vale1, value2 into Table2 from Table1 (it is required that the target table Table2 does not exist because table Table2 is automatically created during insertion and the specified field data in Table1 is copied to table2 .)
Syntax 4: Use the import/export function for full table replication. If you use [write a query to specify the data to be transmitted], then there will be a problem in the replication of big data tables? To a certain extent, the replication will not change, and the memory will burst? It is not written to the table either. Directly executing the preceding three syntaxes will immediately refresh to the database table. You can just refresh the mdf file.

Update data using the Update statement with associated subqueries
Copy codeThe Code is as follows:
-- Method 1:
Update Table1 set c = (select c from Table2 where a = Table1.a) where c is null
-- Method 2:
Update
Set newqiantity = B. qiantity
From A, B
Where A. bnum = B. bnum
-- Method 3:
Update
(Select A. bnum, A. newqiantity, B. qiantity from A left join B on A. bnum = B. bnum) AS C
Set C. newqiantity = C. qiantity
Where C. bnum = XX


Connect to a remote server
Copy codeThe Code is as follows:
-- Method 1:
Select * from openrowset ('sqloledb', 'server = 192.168.0.67; uid = sa; pwd = password', 'select * FROM BCM2.dbo. tbAppl ')
-- Method 2:
Select * from openrowset ('sqloledb', '192. 168.0.67 '; 'sa'; 'Password', 'select * FROM BCM2.dbo. tbAppl ')


Truncate table [Table Name]
The following describes the methods and principles of the Truncate statement in MSSQLServer2000:
Truncate is a SQL statement used to delete data table content. It is used as follows:
The Truncate table name is fast and efficient because:
The truncate table function is the same as the DELETE statement without the WHERE clause: both DELETE all rows in the TABLE. However, truncate table is faster than DELETE and uses less system and transaction log resources.
The DELETE statement deletes a row at a time and records one row in the transaction log. Truncate table deletes data by releasing the data pages used to store TABLE data, and only records the release of pages in transaction logs.
Truncate table deletes all rows in the TABLE, but the TABLE structure, its columns, constraints, and indexes remain unchanged. The Count value used by the new row ID is reset to the seed of the column. To retain the ID Count value, use DELETE instead. To delete TABLE definitions and data, use the drop table statement.
For tables referenced by the foreign key constraint, the truncate table cannot be used, but the DELETE statement without the WHERE clause should be used. Because the truncate table is not recorded in the log, it cannot activate the trigger.
The truncate table cannot be used in the index view.

References
Database Table row-to-column, column-to-row Ultimate Solution

Conversion of rows and columns (Dynamic scripts)

Select into and insert into select table copy statements

Very useful SQL scripts

By: Listening to wind and rain