SQL Row to Column rollup

SQL Row to Column rollup

Pivot is used to rotate column values to column names (row to column), and SQL Server 2000 can be implemented with an aggregate function with a case statement

The general syntax for pivot is: Pivot (aggregate function (column) for column in (...)) As P

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)

SQL2008 can be used directly in the

Full syntax:

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

View Code

UNPIVOT is used to convert columns to column values (that is, column change), which can be implemented with union in SQL Server 2000

Full syntax: Table_sourceunpivot (value_columnfor pivot_columnin (<column_list>))

Typical examples

First, row to column

1. Create a form

IF object_id (' TB ') is not NULL DROP table tbgocreate table TB (name varchar (10), Course VARCHAR (10), fractional INT) insert into TB VALUES ( ' Zhang San ', ' language ', ' the ', ' * * ' INSERT INTO TB values (' Zhang San ', ' math ', ' the ') insert into TB values (' Zhang San ', ' physical ', ') ' INSERT into TB values (' John Doe ', ' language ', ') ' I Nsert into TB values (' John Doe ', ' math ', ' + ') insert into TB values (' John Doe ', ' physical ', 94) goselect * FROM Tbgo

Name Course Score

---------- ---------- -----------

Zhang San language 74

Zhang San Mathematics 83

Zhang San Physics 93

John Doe Language 74

John Doe Mathematics 84

John Doe Physics 94

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 score else 0 end) Math, Max (case course when ' physics ') Then fractions ELSE 0 END) physical from Tbgroup by name

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 ' [Email protected][email protected]+ ', Max ( Case Course when "+ Course +" then score else 0 end) [' + Course + '] ' from (selectdistinct course FROMTB) a--same 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 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)

4. Using SQL Server 2005 static SQL

SELECT * from TB Pivot (MAX (score) for course in (language, mathematics, physics)) a

Name Chinese mathematics Physics

---------- ----------- ----------- -----------

Lee 474 84 94

Sheet 374 83 93

5. Using SQL Server 2005 Dynamic SQL

--using stuff () DECLARE @sql VARCHAR (8000) SET @sql = '  --initialize variable @sqlSELECT @sql = @sql + ', ' + course from TB GROUP by course-Variable multivalued assignment SE T @sql = STUFF (@sql, 1, 1, ")--Remove the first ', ' SET @sql = ' SELECT * from TB pivot (MAX (score) for course in (' [email protected]+ ')) a ' PRINT @sql EXEC (@sql)--or use ISNULL () DECLARE @sql VARCHAR (8000)--Get the course collection Select @sql = ISNULL (@sql + ', ', ') + course from the Tbgroup by course           SET @sql = ' 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

--sql SERVER 2000 static sqlselect name, Max (case course when ' language ' then score else0end) language, max (case course when ' math ' then score else0end) math, Max ( Case course when ' physical ' then fraction else0end) physics, SUM (Score) Total, cast (AVG (score *1.0) Asdecimal (18,2)) Average fromtbgroupby name

The average score of the mathematics physics of the name Chinese

---------- ----------- ----------- ----------- -----------

Lee 474 84 94 252 84.00

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

3. Using SQL Server 2005 static SQL

Select  m.*,        N. Total score,        N. Average from    (SELECT    *          from      TB PIVOT (MAX (score) for course in (language, mathematics, physics)) a
   ) m,        (SELECT    name,                    SUM (Score) Total,                    CAST (avg (Fractional * 1.0) as DECIMAL (18, 2)) Avg.          from      TB          GRO Up by  name        ) nwhere   m. name = N. Name

4. Using SQL Server 2005 Dynamic SQL

View Code

Second, the list of career change

1. Create a form

IF object_id (' TB ') is not NULL DROP table tbgocreate table TB (name VARCHAR (10), language int, math int, physical int) INSERT into TB VALUES (' Zhang Three ', 74,83,93) INSERT into terabytes VALUES (' John Doe ', 74,84,94) goselect * from TB

Name Chinese mathematics Physics

---------- ----------- ----------- -----------

Sheet 374 83 93

Lee 474 84 94

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

Name Course Score

---------- ---- -----------

John Doe Language 74

John Doe Mathematics 84

John Doe Physics 94

Zhang San language 74

Zhang San Mathematics 83

Zhang San Physics 93

2. Using SQL Server 2000 dynamic SQL

3. Using SQL Server 2005 static SQL

--sql SERVER 2005 Dynamic Sqlselect  name,        course,        score from    

4. Using SQL Server 2005 Dynamic SQL

--sql SERVER 2005 Dynamic Sqldeclare @sql NVARCHAR (4000) SELECT  @sql = ISNULL (@sql + ', ', ') + QUOTENAME (name) from    Sysco Lumnswhere   id = object_id (' TB ')        and name not in (' name ') ORDER by Colidset @sql = ' select name, [Course],[score] from TB UNPI Vot ([score] for [course] in (' + @sql    + ')) B ' EXEC (@sql)

SQL Row to Column rollup