SQL grouping Query

Scenario 1:
Table Data
Name score
Aaa 11
Aaa 19
Bbb 12
Bbb 18
Ccc 19
Ddd 21
The expected query result is as follows:
Name score
Aaa 30
Bbb 30
Ccc 19
Ddd 21
Copy codeThe Code is as follows:
--- Check whether the table exists
If exists (select * from sysobjects where name = 'testsum ')
Drop table testSum
Go
--- Create a table
Create table testSum
(
Tid int primary key identity (1, 1 ),
Tname varchar (30) null,
Tscor int null
)
Go
Insert into testSum (tname, tscor)
Select 'aaa', 11
Union all
Select 'aaa', 19
Union all
Select 'bbb ', 12
Union all
Select 'bbb ', 18
Union all
Select 'ccc ', 19
Union all
Select 'ddd ', 21
--- Query statement
Select tname, sum (tscor) from testSum group by tname
--- Only query 30 tscor total
Select tname, sum (tscor) from testSum group by tname having sum (tscor) = 30

Scenario 2:
Name subject score
Zhang San Chinese 30
John's mathematics 50
John's English 70
Li Si language 50
Li Si math 80
Li Si English 90

Expected query results:

Name, Chinese, mathematics, and English
Zhang San 30 50 70
Li Si 50 80 90
Copy codeThe Code is as follows:
--- Check whether the table exists
If exists (select * from sysobjects where name = 'testscore ')
Drop table testScore
Go
--- Create a table
Create table testScore
(
Tid int primary key identity (1, 1 ),
Tname varchar (30) null,
Ttype varchar (10) null,
Tscor int null
)
Go
--- Insert data
Insert into testScore values ('zhang san', 'China', 90)
Insert into testScore values ('zhang san', 'mat', 20)
Insert into testScore values ('zhang san', 'English ', 50)
Insert into testScore values ('Li si', 'China', 30)
Insert into testScore values ('Li si', 'mat', 47)
Insert into testScore values ('Li si', 'English ', 78)
--- Query
Select tname as 'name ',
Max (case ttype when 'China' then tscor else 0 end) 'China ',
Max (case ttype when 'mate' then tscor else 0 end) 'mate ',
Max (case ttype when 'then tscor else 0 end)''
From testScore
Group by tname

Scenario 3:
Table: table1
Field: id, name
Content:
----------------
1, aaa
1, bbb
2, ccc
2, ddd
3, eee
3, fff
--------------
Expected results:
---------------------
1 aaa bbb [aaa bbb is differentiated by halfwidth spaces, similar to the following]
2 ccc ddd
3 eee fff
Copy codeThe Code is as follows:
F exists (select * from sysobjects where name = 'test1 ')
Drop table test1
Go
Create table test1
(
Tid int primary key identity (1, 1 ),
Tnum int null,
Tname varchar (30) null
)
Go
Insert into test1 values (1, 'A ')
Insert into test1 values (1, 'bb ')
Insert into test1 values (2, 'cc ')
Insert into test1 values (2, 'dd ')
Insert into test1 values (3, 'ee ')
Insert into test1 values (3, 'ff ')
SELECT * FROM (select distinct tnum FROM test1
)
Outer apply (
SELECT tname = STUFF (REPLACE (
(
SELECT tname FROM test1 N
WHERE tnum = A. tnum
FOR XML AUTO
), '<N tname = "',''), '"/>',''), 1, 1 ,'')
) N

Scenario 4:
I need to select the data in Table tb to get the data in the second table below. How do I write the select statement?
Table tb
Id a flag class
---------- + --------- + -------- + ---------
1 2 1
2 2 1
3 4 1
4 5 2
5 3 2
6 4 1
7 2 1
8 3 2
9 4 2
10 5 3
11 5 1 B
12 2 1 B
13 3 1 B
14 4 1 B
15 2 3 B
16 7 3 B
17 3 2 B
18 4 1 B
19 5 1 B
20 2 2 B
21 1 1 B
22 1 1 C
23 2 3 C
24 6 3 C
25 3 2 C
...
The following table needs to be selected and grouped by class column. The a1, a2, and a3 fields are the sum of fields a in the tb table when flag = 1, 2, and 3 respectively.
After selection
A1 a2 a3 class
----------- + ------------ + ----------------- + --------------
Sum (a)
Sum (a) B
Sum (a) C
Sum (a) D
Sum (a) E
Sum (a) F
Sum (a) G
Copy codeThe Code is as follows:
--- Check whether the table exists
If exists (select * from sysobjects where name = 'testflag ')
Drop table testFlag
Go
--- Create a table
Create table testFlag
(
Tid int primary key identity (1, 1 ),
Tname varchar (30) null,
Tflag int null,
Tscor int null
)
Go
--- Insert data
Insert into testFlag (tname, tflag, tscor)
Select 'aaa', 1, 11
Union all
Select 'aaa', 2,19
Union all
Select 'aaa', 3, 12
Union all
Select 'aaa', 1, 18
Union all
Select 'aaa', 2,19
Union all
Select 'aaa', 3, 21
Union all
Select 'bbb ', 1, 11
Union all
Select 'bbb ', 2, 19
Union all
Select 'bbb ', 3, 12
Union all
Select 'bbb ', 1, 18
Union all
Select 'bbb ', 2, 19
Union all
Select 'bbb ', 3, 21
---- Query statement
Select distinct tname, (select sum (tscor) from testFlag where tflag = 1 and testFlag. tname = t. tname) as 'flag1', (select sum (tscor) from testFlag where tflag = 2 and testFlag. tname = t. tname) as 'flag2', (select sum (tscor) from testFlag where tflag = 3 and testFlag. tname = t. tname) as 'flag3' from testFlag t group by tname, tflag