Group query, view, and transpose row and column

I:GroupGrouping query results

ExploitationSQLOfGroupClause to quicklyIt is easy to divide the query result table according to the specified fieldGroup, records with the same value are divided into one group.GroupClauseSQLOfAggregate functionsIStart to use.

[Basic format] : Select < Field expression > From < Table Name >Group by <Field name>  Ii. View 1. view definition:(1 ). View is 1 Zhang Virtual table , It indicates part of the data in a table. Or comprehensive data of multiple tables Its structure and data are built on Based on the query of the table(2 ). The view does not store data, but is stored in the reference In the original table (base table)(3 ). For the same original table, you can To create different View  2.View usage(1) Filter table Medium Rows(2) Prevent Unauthorized users from accessing sensitive data(3) Reduce database complexity(4) Set Multiple A physical database is abstracted as a logical database.  3. Transpose rows and columnsDeclare @ sqlstr varchar (8000) set @ sqlstr = 'select [transid], [keyno] '// read the column information to be transposed. Select @ sqlstr = @ sqlstr + ', (select sum ([expr4]) distinct from [best]. [DBO]. [view_temp] group by expr1 having [expr1] = ''' + expr1 + ''') as ['+ expr1 +'] 'from [best]. [DBO]. [view_temp] group by [expr1] // concatenate sqlselect @ sqlstr = @ sqlstr + ', [ssje], [yfje], [mlje], zlje from [view_temp] group by [keyno], [transid], [ssje], [yfje], [mlje], zlje '// execute sqlexec (@ sqlstr) // output the concatenated SQL print @ sqlstr