SQL Row to column summary

Original: SQL row to column summary

 Pivot Unpivot usage pivot is used to rotate column values to column names (row to column), and the general syntax for pivot is implemented in SQL Server 2000 with an aggregate function with a case statement: Pivot (aggregate function (column) for column in (...)) As P full syntax: Table_sourcepivot (Aggregation function (value_column) for Pivot_columnin (<column_list>)) Unpivot is used to set the column value to a column (that is, the column change), in the SQL Server 2000 can use Union to implement the full syntax: Table_sourceunpivot (value_columnfor pivot_columnin (<column_list>)) Note: Pivot, Unpivot is the syntax for SQL Server 2005, use the database compatibility level to modify database properties, and then change the compatibility level to 90 typical instance one, row to column 1, table ifobject_id (' TB ') ISNOTNULLDROPTABLETBGOCREATETABLETB (name varchar (10), course varchar (10), fractional int) insertintotbvalues (' Zhang San ', ' language ', 74) Insertintotbvalues (' Zhang San ', ' math ', ' insertintotbvalues ') (' Zhang San ', ' physics ', ') insertintotbvalues (' John Doe ', ' language ', 74)  Insertintotbvalues (' John Doe ', ' math ', ' insertintotbvalues ') (' John Doe ', ' physics ', 94) goselect*fromtbgo name Course score--------------------       -----------Zhang San language 74 Zhang Three mathematics 83 Zhang three physics 93 Li four languages 74 John Doe Mathematics 84 John Doe Physics 94 2, using SQL Server 2000 static sql--cselect name, Max (case course when ' language ' then fraction else0end) language, max (case course when ' math ' then score else0end) Math, max (case course when ' physical ' then fraction else0end) Physical fromtbgroupby name name Chinese mathematical Physics-------------------------------- -----------Lee 474 84 94 374 83 93 3, using SQL Server 2000 Dynamic Sql--sql server 2000 dynamic SQL, refers to the course of more than language, mathematics, physics, this course. (hereinafter)-variables are assigned in SQL language order [email protected] [email protected]= ' select name ' sele[email protected][ Email protected]+ ', max (case course when ' + course + ' then score else 0 end) [' + Course + '] ' from (selectdistinct course FROMTB) a--with from TB Group by course, default by course name [email protected][email protected]+ ' from TB Group by name ' EXEC (@sql)--using IsNull (), Variables first determine the dynamic part [email protected] (8000) [Email protected]=isnull (@sql + ', ', ') + ' max (case course when ' + Course + ') Then score else 0 end) [' + Course + '] ' from (selectdistinct course FROMTB) ASA [email protected]= ' select name, ' [email protected           ]+ ' from TB Group by name ' EXEC (@sql) name-------------------------------------------Li 484, mathematical and physical language 94 74 Photos 383 93 74 4, using SQL Server 2005 static SQLSELECT*FROMTB pivot (max (score) for course in (language, math, physics)) a 5, using SQL Server 2005 dynamic sql--using Stuff () [E Mail protected] (8000) [email protected]= '--Initialize variable @sql[email protected][email protected]+ ', ' + Course FROMTBGROUPBY Course-variable multi-value assignment [Email protected]=stuff (@sql, 1, 1, ')--minus the first ', ' [email protected]= ' select * From TB Pivot (MAX (score) for course in (' [email protected]+ ')) a ' EXEC (@sql)--or use IsNull () [email protected] (8000) –- Get a collection of courses [Email protected]=isnull (@sql + ', ', ') + Course fromtbgroupby Course [email protected]= ' select * from TB pivo T (Max (score) for course in (' [email protected]+ ')) a ' exec (@sql) Two, row to column results plus total score, average 1, using SQL Server 2000 static SQL--SQL server 2000 Static Sqlselect name, Max (case course when ' language ' then fraction else0end) language, max (case course when ' math ' then score else0end) Math, max (case course when ' physics ')        Then fraction else0end) physics, SUM (Score) Total, cast (AVG (score *1.0) Asdecimal (18,2)) average Fromtbgroupby name name Chinese mathematics Physics total Score Average------------------------------------------------------Lee 474          84 94 252 84.00 Sheets 374 83 93 250 83.33 2, using SQL Server 2000 Dynamic Sql--sql Server 2000 dynamic sql[email protected] [email protected]= ' select name ' [email  Protected][email protected]+ ', max (case course when ' + course + ' then score else 0 end) [' + Course + '] ' from (selectdistinct course FROMTB ) a[email protected][email protected]+ ', sum (score) Total, cast (avg (fractional *1.0) as decimal (18,2)) average by name ' from TB Group by ' EXEC (@sql) 3, using SQL Server 2005 static sqlselectm.*,n. Score, N. Average from (SELECT*FROMTB pivot (max (score) for course in (language, mathematics, physics)) m, ( Select name, sum (score) Total, cast (AVG (score *1.0) Asdecimal (18,2)) average fromtbgroupby name Nwherem. Name =n. Name 4, using SQL Server 2005 Dynamic sql--using Stuff ()--[email protected] (8000) [email protected]= '--Initialize variable @sql[email protected][ email protected]+ ', ' + course FROMTBGROUPBY Course-variable Multi-value assignment-Same as Select @sql = @sql + ', ' + course from (SELECT DISTINCT course from TB) a[ Email protected]=stuff (@sql, 1, 1, ")--minus the first ', ' [email protected]= ' select m.*, N. Total score, N. AverageFrom (SELECT * to (SELECT * from TB) a pivot (max (fractional) for course in (' [email protected]+ ')) b) m, (select name, sum (Score) Total, CAs T (avg (fractional *1.0) as decimal (18,2)) (average) nwhere m. name = N. Name ' exec ' (@sql)-or use IsNull () [email protected] ( 8000) [Email protected]=isnull (@sql + ', ', ') + Course fromtbgroupby Course [email protected]= ' select m.*, N. Total, N. Average from (SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (' + @sql + ')) b) m, (select name, sum (score) Total, cast (avg.) 0) as decimal (18,2)) nwhere m. Name = N. Name ' exec ' (@sql) second, row 1, Form ifobject_id (' TB ') ISNOTNULLDROPTABLETBGOCREATETABLETB (name varchar (10), language int, math int, physical int) insertintotbvalues (' Zhang San ', 74,83,93) Insertintotbvalues (' John Doe ', 74,84,94) goselect*fromtbgo name Chinese mathematical physics-------------------------------------- -----374 83 93 Lee 474 84 94 2, using SQL Server 2000 static SQL--SQL Server 2000 Static sq L Select*from (select name, course = ' language ', score = language FROMTB UnionAll select name, course = ' math ', score = Math FROMTB UniOnall Select name, course = ' physical ', score = physical FROMTB) Torderby name, case course when ' language ' then1when ' math ' then2when ' Physics ' then3end name course score----------- --------------John Doe language 74 John Doe Mathematics 84 John Doe Physics 94 three languages 74 Zhang three Mathematics 83 sheets three Physics 93 2, using SQL Server 2000 dynamic Sql--sql SERVER 2000 Dynamic SQL. --Call system table dynamic Ecology. [Email protected] (8000) [Email protected]=isnull (@sql + ' union All ', ') + ' select name, [Course]= ' +quotename (name, ' "') + ', [score] = ' +quotename ( Name) + ' from TB ' fromsyscolumnswherename!= ' name ' andid=object_id (' TB ')--table name TB, not including other columns named Name Orderbycolidexec (@sql + ' Order by name ') go 3, use SQL Server 2005 static SQL--SQL Server 2005 Dynamic Sqlselect name, course, score fromtb Unpivot (score for course in ([Language],[Math],[physics]) t 4. Using SQL Server 2005 Dynamic SQL--SQL Server 2005 dynamic sql[email protected] (4000) [Email protected]=isnull (@sql + ', ', ' ') +quotename (name) fromsyscolumnswhereid=object_id (' TB ') andnamenotin (' name ') orderbycolid[email protected]= '  Select Name, [Course],[score] from TB Unpivot ([score] for [course] in (' [email protected]+ ')) B ' EXEC (@sql)

SQL Row to column summary