SQL Server row to column (PIVOT), column change (UNPIVOT) Summary

Pivot is used to rotate column values to column names (row to column)

Grammar:

Table_sourcepivot (Aggregation function (value_column) for Pivot_columnin (<column_list>))

Unpivot used to set the column value to a column (that is, a column change)

Grammar:

Table_sourceunpivot (value_columnfor pivot_columnin (<column_list>))

Note: PIVOT, Unpivot is the syntax for SQL Server 2005, use the database compatibility level that you want to modify
In the database properties, options, compatibility level, change to 90

First, row to column

1. Create a form

ifobject_id (' TB ') isnotnulldroptabletbgocreate table TB (name varchar (10), course varchar (10), fractional int) Insert into TB values (' Zhang San ' INSERT into TB values (' Zhang San ', ' math ', ', ') insert into TB values (' Zhang San ', ' physical ', ') insert into TB values (' John Doe ', ' language ', 74) INSERT into TB values (' John Doe ', ' math ', ' + ') insert into TB values (' John Doe ', ' physical ', 94) goselect * from TB

2. Using SQL Server 2000 static SQL

Select name, max (case course when ' language ' then score else 0 end) language, Max (case course when ' math ' then fraction else 0 end) Math, Max (case course when ' physics ' then fraction else 0 End) physical from Tbgroup by name

3. Using SQL Server 2000 dynamic SQL

[Email protected] (500) [Email protected]= ' SELECT name ' [Email protected][email protected]+ ', max (case course when ' + course + ' then score else 0 end) [' + Course + '] ' From (selectdistinct course FROMTB) a--same as from TB Group by course, sorted by course name by default [email Protected][email protected]+ ' from TB Group by name ' exec (@sql)

--Using IsNull (), variables determine the dynamic part first

[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)

4. Using SQL Server 2005 static SQL

SELECT*FROMTB Pivot (max (score) for course in (language, mathematics, physics)) a

5. Using SQL Server 2005 Dynamic SQL

--using stuff () [email protected] (8000) [email protected]= '  --Initialize variable @sql[email protected][email protected]+ ', ' + Course FROMTBGROUPBY-Variable multi-value assignment [email protected]=stuff (@sql, 1, 1, ")--Remove 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 pivot (MAX (score) for course in (' [email protected]+ ')) a ' EXEC (@sql)

The results of the row and column are combined with the total score, the average

1. Using SQL Server 2000 static SQL

Select name, max (case course when ' language ' then fraction else0end) language, max (case course when ' math ' then fraction 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

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 ' exec ' (@sql)

3. Using SQL Server 2005 static SQL

4. Using SQL Server 2005 Dynamic SQL

[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, ")--Remove the first ', ' [email protected]= ' Select m . *, N. Total score, N. Average from (SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (' [email protected]+ ')) b) m, (select name, SUM ( Score) Total, cast (avg (fractional *1.0) as decimal (18,2)), average from TB group by name nwhere m. name = N. Name ' exec ' (@sql)-or use ISNULL () [Email protecte D] (8000) [Email protected]=isnull (@sql + ', ', ') + course fromtbgroupby Course [email protected]= ' Select m.*, N. Total score, 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 (fractional *1.0) as Deci Mal (18,2)) nwhere m. Name = N. Name ' exec ' (@sql)

Second, the list of career change

1. Create a 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*FROMTB

2. Using SQL Server 2000 static SQL

--sql SERVER 2000 Static SQL. Select*from (select name, course = ' language ', score = language FROMTB UnionAll select name, course = ' math ', score = Math fromtb UnionAll Select name, course = ' physics ', score = Physical FROMTB) Torderby name, case course when ' language ' then1when ' math ' then2when ' physics ' then3end

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) + ' FR Om TB ' fromsyscolumnswherename!= ' name ' andid=object_id (' TB ')--table name TB, does not include other columns named Name Orderbycolidexec (@sql + ' ORDER by name ')

3. Using 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 Server row to column (PIVOT), column change (UNPIVOT) Summary