Learning SQL statements (strong group by and select from modes)

Powerful group
Copy codeThe Code is as follows:
Select stdname,
Isnull (sum (case stdsubject when 'chemistry 'then Result end), 0) [chemistry],
Isnull (sum (case stdsubject when 'mate' then Result end), 0) [math],
Isnull (sum (case stdsubject when 'physical 'then Result end), 0) [physical],
Isnull (sum (case stdsubject when 'China' then Result end), 0) [language]
From # student
Group by stdname

Combined with sum + case, group by can change the record (ROW) in table 1 to the field (column) in table 2 ). If there is no case in Sum, the value can only be the sum of all subjects. After case is used, the result of a certain subject is used. Then, here we use several Sum, each subject has a sum, so in Table 1, the "row" of a certain subject occupies a record is changed to a record of someone in table 2, and each subject has a field.

Use the select from (select from) mode to generate an SQL statement
Copy codeThe Code is as follows:
Declare @ SQL varchar (4000)
Set @ SQL = 'select stdname'
Select @ SQL = @ SQL + ', isnull (sum (case stdsubject when ''' + stdsubject + ''' then Result end), 0) [' + stdsubject + ']'
From (select distinct stdsubject from # student) as
Select @ SQL = @ SQL + 'from # student group by stdname'
Print @ SQL
Exec (@ SQL)


Copy codeThe Code is as follows:
Select [name] into # tmpCloumns
From tempdb. dbo. syscolumns
Where id = object_id ('tempdb. dbo. # student2 ')
And [name] <> 'stdname'
Select * from # tmpCloumns

Declare @ strSql nvarchar (800)
Select @ strSql =''
Select @ strSql = @ strSql + 'Union all' + char (10) + char (13) +
'Select [stdname], ''' + [name] + ''' as [subject], ['+ [name] +'] '+ char (10) + char (13) +
'From [# student2] '+ char (10) + char (13)
From # tmpCloumns

Select @ strSql = substring (@ strSql, 11, len (@ strSql) + 'order by stdname, [subject]'
Exec (@ strsql)

The preceding excerpted SQL code from some posts on the Internet. I saw it a long time ago. I don't know the original link.