PIVOT与用法解释,行列转换

伊始简单来讲:PIVOT就是行转列,UNPIVOT就是列传行

1,数据库的生命周期:
须求分析(须求表达书) — 逻辑设计(E-R) — 物理设计(表结构) —
数据库落成(范式化) — 数据修改(索引/约束) — 数据库监控(维护)
详细可参照:

SQL Server 行转列

转自:

在数据库操作中,有些时候大家遭受必要贯彻“行转列”的急需,例如一下的表为某商店的七天收入意况表:

2,E-R图学习:列出一些主导新闻,我也是那里上学的

在SQL Server 2005中PIVOT 用于将列值转换为列名(行转列),在SQL Server
2000中是未曾这一个主要字的 只可以用case语句达成。

PIVOT 用于将列值旋转为列名(即行转列),在 SQL
Server 2000足以用聚合函数合营CASE语句达成

WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL)

3,主键:唯一标识列
特点:字段非空
字段具有唯一性
该值不允许修改(提议|【ps:改仍旧有方法改的】)
每个实体只可以有一个主键(提议|【ps:也足以有一道主键】)
外键:连接不相同实体
功效:保持数据完整性

--创建测试数据库
use master
go
if ( exists (select * from sys.databases where name = 'webDB') )
    drop database webDB
go
create database webDB on primary
(
    name = 'webDB',
    filename = 'f:\database\webDB.mdf',
    size = 5mb,
    maxsize = unlimited,
    filegrowth = 10%
)
log on
(
    name = 'webDB_log',
    filename = 'f:\database\webDB_log.ldf',
    size = 3mb,
    maxsize = 50mb,
    filegrowth = 2mb
)

use webDB
go

--创建测试表
if( exists ( select * from sys.objects where name = 'student'))
    drop table student
go
create table student
(
    id int identity(1,1) primary key,
    name varchar(20) not null,
    subject varchar(20) not null,
    score int not null
)    

--插入测试数据
insert into student values ('张三','语文',90),
('张三','数学',100),
('张三','英语',80),
('李四','英语',90),
('王五','语文',90),
('李四','语文',90),
('李四','数学',70),
('王五','数学',62),
('王五','英语',82)

select * from student

PIVOT 的相似语法是:PIVOT(聚合函数(列) FOR 列 in (…) )AS P

咱俩先插入一些仿照数据:

4,数据库的三大范式(控制数据的冗余)
首先范式(1NF):确保每列保持原子性
第二范式(2NF): 确保表中的每列都和主键相关
其三范式(3NF): 确保每列都和主键列直接相关,而不是直接相关
详细可参看:
【PS:博主有话说>>通过特有提供冗余数据,下跌连接的复杂度,获取更快的查询时间。
那时,撤消规范化也是必须的。
看来,质量 > 规范】

997755.com澳门葡京 1

留神:PIVOT、UNPIVOT是SQL Server
2005 的语法,使用需修改数据库包容级别(在数据库属性->选项->包容级别改为
  90 )

INSERT INTO WEEK_INCOME 
SELECT '星期一',1000UNION ALLSELECT '星期二',2000UNION ALLSELECT '星期三',3000UNION ALLSELECT '星期四',4000UNION ALLSELECT '星期五',5000UNION ALLSELECT '星期六',6000UNION ALLSELECT '星期日',7000

5,增删改查格式:
增:INSERT INTO 表名(列名) VALUES (值)
删:DELETE FROM 表名 WHERE 条件
【PS:条件一旦没加,则删除整张表数据。谨慎操作】
改:UPDATE 表名 SET 字段 = ‘值’ WHERE 条件
【PS:条件一旦没加,则修改整张表数据。谨慎操作】

SQL Server 2000 行转列

SQL2008 中得以向来运用

一般我们最日常利用的询问是询问七天中每日或某几天的收益,例如查询周天至周六一切的纯收入:

查:SELECT * FROM 表名 WHERE 条件

【PS * : 表示检索指定表中的兼具列】

6, GROUP BY 子句1:用于分组输出游
ORDER BY 子句2:排序【DESC | ASC】
语句1:
–解释:按照name来分组,并且根据name来排序

select name as 姓名,
SUM(case [subject] when '语文' then score else 0 end) as '语文',
SUM(case [subject] when '数学' then score else 0 end ) as '数学',
SUM(case [subject] when '英语' then score else 0 end ) as '英语'
from student group by name

全部语法:

SELECT WEEK,INCOME FROM WEEK_INCOME

–【PS:使用group by 时,查询的非聚合函数都要在group by中】

SELECT NAME FROM USERS GROUP BY NAME ORDER BY NAME DESC

7,PIVOT: 将行旋转成列

997755.com澳门葡京 2

table_source

PIVOT(

聚合函数(value_column)

FOR pivot_column

IN(<column_list>)

)

取得如下的询问结果集:

语句2:

SELECT
[星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]
–那里是PIVOT第三步(拔取行转列后的结果集的列)那里可以用“*”表示选取具有列,也得以只接纳一些列(也就是某些天)
FROM WEEK_INCOME
–那里是PIVOT第二手续(准备原始的查询结果,因为PIVOT是对一个原来的询问结果集进行转移操作,
–所以先查询一个结出集出来)那里可以是一个select子查询,但为子查询时候要指定别名,否则语法错误
PIVOT
(
SUM(INCOME) for [week]
in([PIVOT与用法解释,行列转换。星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])
–那里是PIVOT第一步骤,也是基本的地点,进行行转列操作。
–聚合函数SUM表示您需要哪些处理转换后的列的值,是总和(sum),
–如故平均(avg)仍然min,max等等。例如假若week_income表中有两条数据同时其week都是“星期天”,
–其中一条的income是1000,另一条income是500,那么在此处运用sum,行转列后“周三”这么些列的值当然是1500了。
–后面的for [week] in([星期一],[星期二])中 for
[week]说是将week列的值分别转换成一个个列,
–也就是“以值变列”。不过必要转换成列的值有可能有众多,大家只想取中间多少个值转换成列,那么哪些取呢?
–就是在in里面了,比如自己此刻只想看工作日的受益,在in里面就只写“周天”至“周六”
–(注意,in里面是原本week列的值,”以值变列”)。
–总的来说,SUM(INCOME) for [week]
in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])
–那句的意思如若直译出来,
–就是说:将列[week]值为”周一”,”星期天”,”星期六”,”星期天”,”周六”,”星期一”,”周五”分别转换成列,
–那些列的值取income的总数。

如图所示,已经根据脚本中指定的列名进行转换,但如此做必要驾驭表中都有啥样数据足以当做列。平常将这种方法称为静态方法。

UNPIVOT 用于将列明转为列值(即列转行),在SQL Server
2000可以用UNION来促成

WEEK           INCOME
星期一           1000
星期二           2000
星期三           3000
星期四           4000
星期五           5000
星期六           6000
星期日           7000

)TBL–别名一定要写

declare @sql varchar(1000)
set @sql = 'select name as 姓名 , '
select @sql = @sql + 'sum(case [subject] when ''' + [subject] + ''' then score  else 0 end ) as '''
+ QUOTENAME([subject]) + ''',' from (select distinct [subject] from student ) as s --后加逗号,然后截取最后一个逗号
select @sql = LEFT(@sql,len(@sql)-1) + ' from student group by name '

print(@sql)
exec(@sql)

select QUOTENAME('aa[]bb') --其中quotename 用于将字符串为有效的标识符
完整语法:

table_source

UNPIVOT(

value_column

FOR pivot_column

IN(<column_list>)

)

但是在一部分情景下(往往是某些报表中),大家目的在于在一行中突显星期五至周日的入账,那时候查询结果集应该是这么的:

【PS:以下语句as的意思是给列起别名的意思】

语句3:
select [1] as ‘v1’,[2],[3],[4],[5],[6],[7],[8]
from (
select PublisherId
from books
) as tb_source
pivot(
count(PublisherId) for PublisherId in
([1],[2],[3],[4],[5],[6],[7],[8])
) as tb_pivot

8,ROW_NUMBER(): 重返一个唯一的种类号
【ps:要配合over(order by 列名);】

997755.com澳门葡京 3

非凡实例

星期一   星期二   星期三   星期四   星期五   星期六   星期日
1000     2000     3000     4000     5000     6000     7000

语句4:

SELECT ROW_NUMBER() OVER(ORDER BY ID),* FROM Books

9,PARTITION BY 子句3:用于将结果集划分为利用了ROW_NUMBER()函数的分区
每个分区的第一行都是从1起首的

那种艺术不须要精通究竟须要将怎样数据作为列转换,它会自行去数据中追寻不重复的数量,都会作为列来呈现。平时将那种艺术称为动态方法,拼接sql方法。

一、行转列

那种情状下,SQL查询语句可以如此写:

语句5:

SELECT ROW_NUMBER() OVER(PARTITION BY PublisherId ORDER BY PublisherId)
as rownumber,*
FROM Books WHERE PublisherId in (1,2,3)

10,通配符:% _ [] [^]

SQL Server 2005 行转列

1、建立表格

SELECTSUM(CASE WEEK WHEN '星期一' THEN INCOME END) AS [星期一],
SUM(CASE WEEK WHEN '星期二' THEN INCOME END) AS [星期二],
SUM(CASE WEEK WHEN '星期三' THEN INCOME END) AS [星期三],
SUM(CASE WEEK WHEN '星期四' THEN INCOME END) AS [星期四],
SUM(CASE WEEK WHEN '星期五' THEN INCOME END) AS [星期五],
SUM(CASE WEEK WHEN '星期六' THEN INCOME END) AS [星期六],
SUM(CASE WEEK WHEN '星期日' THEN INCOME END) AS [星期日]FROM WEEK_INCOME

语句6:查找用户名以L开首的用户音信

SELECT * FROM USERS WHERE USERNAME LIKE ‘L%’

11,聚合函数:SUM() –求和
COUNT() –求数量
MIN() — 最小值
MAX() — 最大值
AVG() — 平均数
ROW_NUMBER() — 获取系列号

12,日期函数:
CURRENT_TIMESTAMP –2017-11-23 19:11:03.320
GETDATE() –2017-11-23 19:11:03.320
DATEPART(YEAR, GETDATE()) –2017
YEAR(GETDATE()) –2017

13,表连接:
13.1 内连接: 语法1:SELECT * FROM A INNER JOIN B ON A.ID = B.AID;

select * from (
    select name,[subject],score from student
) s pivot (sum(score) for [subject] in (语文,数学,英语)) as pvt
order by pvt.name
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb

go

CREATE TABLE tb(姓名 VARCHAR(10),课程 VARCHAR(10),分数 INT)

insert into tb VALUES ('张三','语文',74)
insert into tb VALUES ('张三','数学',83)
insert into tb VALUES ('张三','物理',93)
insert into tb VALUES ('李四','语文',74)
insert into tb VALUES ('李四','数学',84)
insert into tb VALUES ('李四','物理',94)

go

SELECT * FROM tb

go

唯独,在SQL SERVER
2005中提供了更为便捷的法门,这就是”PIVOT”关系运算符。(相反的“列转行”是UNPIVOT),以下是应用PIVOT落成“行转列”的SQL语句

语法2:SELECT * FROM A, B WHERE A.ID = B.ID

【PS:以下语句是为表起别名,并且询问指定表的指定字段音信】

PIVOT语法是:PIVOT(聚合函数(列) for 列 in (值,值,值)) as p

姓名       课程       分数

SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]FROM WEEK_INCOME
PIVOT
(
    SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])
)TBL

SELECT a.c1, b.c1 FROM A a INNER JOIN B b ON A.ID = B.AID;

13.2 外连接:
13.2.1 左连接:SELECT * FROM A LFET JOIN B ON A.ID = B.AID;
13.2.2 右连接:SELECT * FROM A RIGHT JOIN B ON A.ID = B.AID;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
【PS:左连接 与 右连接 是有分其他,】详情可查阅:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
13.3 全连接 :SELECT * FROM 表1 FULL JOIN 表2 ON 表1.ID = 表2.表1_ID;

14,UNION JOIN :创建一个饱含多少个表中所有行的表
SELECT *997755.com澳门葡京 , FROM A union JOIN B ON A.ID = B.AID;
等价于:
SELECT * FROM A UNION ALL SELECT * FROM B
【PS:两表必须有一样数量的列,对应的数据类型要合营】

15,关于空行:

其一是静态方法行转列,如何代码简洁吧。


请参考MSDN中关于PIVOT的用法:

语句7:(查询用户名不为空的数据行)【IS NULL : 与 IS NOT NULL 相反】

SELECT * FROM USERS WHERE NAME IS NOT NULL
【PS: NAME = NULL 与 NAME IS NULL 是差其余】

declare @sql_str varchar(1000)
declare @sql_col varchar(1000)
select @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME([subject]) from student group by [subject] --先确定要转换的列名
set @sql_str = '
select * from (
    select name,[subject],score from student 
) s pivot (sum(score) for [subject] in (' + @sql_col + ')) as pvt
order by pvt.name'
print(@sql_str)
exec(@sql_str)

张三       语文        74

语句8:查询用户表中的用户名,若为空,则用‘未知’替代

SELECT ISNULL(Name, ‘未知’) FROM USERS

16,BETWEEN…AND… / IN() / NOT IN() –范围性条件

17,条件逻辑运算符:AND / OR / NOT

18,成立新表1
语句9:创制一个临时表【#temp: 表名加#,表明创制临时表,否则为永久表|

上述2005中动态创造方法。

张三       数学        83

只是MSDN上的描述太过火正统严穆,我看了半天还没弄了解什么利用PIVOT,搞不清楚PIVOT里面的语法的含义。于是又google了成百上千资料,以及通过地点提到的WEEK_INCOME表例子作了试验,最后搞领会了其用法。在网上有篇博文解释的很好:T-SQL
PIVOT語法剖析與實戰,基本上我要写的就是参考该博文,再添加自己一点个体知道。

临时表的周期:查询窗口关闭即销毁】

SELECT ID, USERNAME, LOGINID, EMAIL INTO #TEMP FROM USERS

语句10:用途:能够复制一个表结构:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT * INTO USERS_L FROM USERS
–复制表结构,同时复制表数据,然则无约束,无主键
SELECT * INTO USERS_L FROM USERS WHERE 1=0
–复制表结构,可是不复制表数据,无束缚
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

19,操作数据:
创立表后,插入数据:使用第5点INSERT INTO;
【PS:插入的数量中,有外键列存在的话,则插入的数目在外键表中必须存在,否则插入失败。–[保险数据完整性]】

履新数据:
立异一条数据中的三个字段:

SQL Server 列转行

张三       物理        93

要精通PIVOT语法,就是要通晓微软何以如此设计PIVOT,但自我相信是现实性要求催生设计思路,所以算是大家照旧要弄领悟哪些是“行转列”:

语句11:

UPDATE A SET NAME=’JAKE’,LOGINID=’10001′ WHERE ID = 1

去除数据:

在SQL Server 2005中UNPIVOT用于将列名转换为值(列转行),在SQL Server
2000中不得不用UNION语句完结。

李四       语文        74

健康状态下的查询结果是那样:

语句12:

DELETE FROM USERS WHERE ID = 1;
数据量大时,可用:TRUNCATE TABLE
【PS:TRUNCATE不履行日志操作,不帮助復苏删除的数目】
【网上说选择delete误删除的:
可以行使mdfview程序回复,下载地址:
【我没试过…】

use webDB
go
--创建测试表
if( exists ( select * from sys.objects where name = 'student'))
    drop table student
go
create table student
(
    id int identity(1,1) primary key,
    name varchar(20) not null,
    语文 int not null,
    英语 int not null,
    数学 int not null
)    

--插入测试数据
insert into student values ('张三',87,90,62),
('李四',87,90,65),
('王五',23,90,34)

select * from student

李四       数学        84

星期一           1000
星期二           2000
星期三           3000
星期四           4000
星期五           5000
星期六           6000
星期日           7000

997755.com澳门葡京 4

李四       物理        94

行转列后是这么:

SQL Server 2000中列转行

 

星期一   星期二   星期三   星期四   星期五   星期六   星期日
1000    2000    3000    4000    5000    6000    7000

SQL Server 2000 静态方法

2、使用SQL Server 2000静态SQL

也就是说,行转列后,原来的某部列的值变做了列名,在那边就是原本WEEK列的值“星期二”,”周一”…”周二”边做了列名,而我们须要做的另一个干活就是计量那一个列的值(那里的“统计”其实就是PIVOT里面的聚合函数(sum,avg等))

select * from (
    select name,课程='语文',分数=语文 from student
    union all
    select name,课程='数学',分数=数学 from student
    union all
    select name,课程='英语',分数=英语 from student
) t order by name, case 课程 when '语文' then 1 when '数学' then 2 when '英语' then 3 end
SELECT 姓名,

 max(CASE 课程 WHEN'语文' THEN 分数 ELSE 0 END) 语文,

 max(CASE 课程 WHEN'数学' THEN 分数 ELSE 0 END) 数学,

 max(CASE 课程 WHEN'物理' THEN 分数 ELSE 0 END) 物理

FROM tb

GROUP BY 姓名

现在结合注释来分析一下PIVOT语法(在那从前最赏心悦目看我上面提到博文:T-SQL
PIVOT語法剖析與實戰,里面说到的PIVOT语法的多少个步骤挺紧要):

997755.com澳门葡京 5

3、使用SQL Server 2000动态SQL

SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]--这里是PIVOT第三步(选择行转列后的结果集的列)这里可以用“*”表示选择所有列,也可以只选择某些列(也就是某些天)FROM WEEK_INCOME --这里是PIVOT第二步骤(准备原始的查询结果,因为PIVOT是对一个原始的查询结果集进行转换操作,所以先查询一个结果集出来)这里可以是一个select子查询,但为子查询时候要指定别名,否则语法错误PIVOT
(
    SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])--这里是PIVOT第一步骤,也是核心的地方,进行行转列操作。聚合函数SUM表示你需要怎样处理转换后的列的值,是总和(sum),还是平均(avg)还是min,max等等。例如如果week_income表中有两条数据并且其week都是“星期一”,其中一条的income是1000,另一条income是500,那么在这里使用sum,行转列后“星期一”这个列的值当然是1500了。后面的for [week] in([星期一],[星期二]...)中 for [week]就是说将week列的值分别转换成一个个列,也就是“以值变列”。但是需要转换成列的值有可能有很多,我们只想取其中几个值转换成列,那么怎样取呢?就是在in里面了,比如我此刻只想看工作日的收入,在in里面就只写“星期一”至“星期五”(注意,in里面是原来week列的值,"以值变列")。总的来说,SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])这句的意思如果直译出来,就是说:将列[week]值为"星期一","星期二","星期三","星期四","星期五","星期六","星期日"分别转换成列,这些列的值取income的总和。
)TBL--别名一定要写

SQL Server 2000 动态SQL

--SQL SERVER 2000动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)

--变量按sql语言顺序赋值
declare @sql varchar(500)

set @sql='select 姓名'

select @sql=@sql+',max(case 课程 when '''+课程+''' then 分数 else 0 end)['+课程+']'

from(select distinct 课程 from tb) a--同from tb group by课程,默认按课程名排序

set @sql=@sql+' from tb group by 姓名'

exec(@sql)

--使用isnull(),变量先确定动态部分

declare @sql varchar(8000)

select @sql=isnull(@sql+',','')+' max(case 课程 when '''+课程+''' then 分数 else 0 end) ['+课程+']'

from(select distinct 课程 from tb) as a      

set @sql='select 姓名,'+@sql+' from tb group by 姓名'

exec(@sql)

以上是自己对PIVOT的明白,我尽所能表明出来。然而话说回来,个人的了解的法门也不相同,就像本人起来看了很多篇博文,都未曾搞了然PIVOT用法。结果要么硬的经过例子和外人的博文再增进思考才弄懂了,所以一旦各位看了本篇之后仍不可能知晓,那很正规,协作例子再增加自己思想,逐步的定能领悟。

declare @sql varchar(1000)
select @sql = ISNULL(@sql + ' union all ','') + ' select name,课程='
+ QUOTENAME(name,'''')+' , 分数 = ' + QUOTENAME(name) + ' from student' from syscolumns 
where id=object_id('student') and name not in ('id','name')
print(@sql)
exec(@sql)

4、使用SQL Server 2005静态SQL

 

SQL Server 2005 静态SQL 使用UNPIVOT关键字

SELECT * FROM tb pivot( MAX(分数) FOR 课程 IN (语文,数学,物理))a
select name,课程,分数 from student unpivot (分数 for 课程 in (语文,英语,数学)) s

姓名       语文        数学        物理

997755.com澳门葡京 6


SQL Server 2005 动态SQL

李四        74          84          94

declare @sql varchar(1000)
select @sql = isnull(@sql + ',','') + quotename(name) from syscolumns 
where id = object_id('student') and name not in ('id','name')
order by colid
set @sql = 'select name,课程,分数 from student unpivot (分数 for 课程 in ('+@sql+')) s'
print(@sql)
exec(@sql)

张三        74          83          93

 

5、使用SQL Server 2005动态SQL

--使用stuff()

DECLARE @sql VARCHAR(8000)

SET @sql=''  --初始化变量 @sql

SELECT @sql= @sql+',' + 课程 FROM tb GROUP BY 课程 --变量多值赋值

SET @sql= STUFF(@sql,1,1,'')--去掉首个','

SET @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'

PRINT @sql

exec(@sql)

--或使用isnull()

DECLARE @sql VARCHAR(8000)

--获得课程集合

SELECT @sql= ISNULL(@sql+',','')+课程 FROM tb
GROUP BY 课程           

SET @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'

exec(@sql)

二、行转列结果加上总分、平均分

1、使用SQL Server 2000静态SQL

--SQL SERVER 2000静态SQL

select 姓名,

max(case 课程 when '语文' then 分数 else 0 end)语文,

max(case 课程 when '数学' then 分数 else 0 end)数学,

max(case 课程 when '物理' then 分数 else 0 end)物理,

sum(分数)总分,

cast(avg(分数*1.0) as decimal(18,2))平均分

from tb

group by 姓名

姓名       语文        数学        物理        总分        平均分


李四        74          84          94          252         84.00

张三        74          83          93          250         83.33

 

2、使用SQL Server 2000动态SQL

--SQL SERVER 2000动态SQL

declare @sql varchar(500)

set @sql='select 姓名'

select @sql=@sql+',max(case 课程 when '''+课程+''' then 分数 else 0 end)['+课程+']'

from(select distinct 课程 from tb)a

set @sql=@sql+',sum(分数) 总分,cast(avg(分数*1.0) as decimal(18,2)) 平均分 from tb group by 姓名'

exec(@sql)

3、使用SQL Server 2005静态SQL

SELECT  m.* ,
        n.总分 ,
        n.平均分
FROM    ( SELECT    *
          FROM      tb PIVOT( MAX(分数) FOR 课程 IN ( 语文, 数学, 物理 ) ) a
        ) m ,
        ( SELECT    姓名 ,
                    SUM(分数) 总分 ,
                    CAST(AVG(分数 * 1.0) AS DECIMAL(18, 2)) 平均分
          FROM      tb
          GROUP BY  姓名
        ) n
WHERE   m.姓名 = n.姓名

4、使用SQL Server 2005动态SQL

--使用stuff()
DECLARE @sql VARCHAR(8000)

SET @sql = ''
  --初始化变量@sql

SELECT  @sql = @sql + ',' + 课程
FROM    tb
GROUP BY 课程
--变量多值赋值

--同select @sql = @sql + ','+课程 from (select distinct 课程 from tb)a

SET @sql = STUFF(@sql, 1, 1, '')
--去掉首个','

SET @sql = ' select m.* , n.总分,n.平均分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql
    + ')) b) m ,

(select 姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2)) 平均分 from tb group by 姓名) n

where m.姓名= n.姓名'

EXEC(@sql)

--或使用isnull()

DECLARE @sql VARCHAR(8000)

SELECT  @sql = ISNULL(@sql + ',', '') + 课程
FROM    tb
GROUP BY 课程

SET @sql = 'select m.* , n.总分,n.平均分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql
    + ')) b) m ,

(select 姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2)) 平均分 from tb group by 姓名) n

where m.姓名= n.姓名'

EXEC(@sql)

 

二、列转行

1、建立表格

IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
go

CREATE TABLE tb(姓名 VARCHAR(10),语文 INT,数学 INT,物理 INT)

INSERT INTO tb VALUES('张三',74,83,93)

INSERT INTO tb VALUES('李四',74,84,94)

go

SELECT * FROM tb

姓名       语文        数学        物理


张三       74          83          93

李四        74          84          94

2、使用SQL Server 2000静态SQL

--SQL SERVER 2000静态SQL。

select * from

(

 select 姓名,课程='语文',分数=语文 from tb

 union all

 select 姓名,课程='数学',分数=数学 from tb

 union all

 select 姓名,课程='物理',分数=物理 from tb

) t

order by 姓名,case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end

姓名       课程 分数


李四       语文 74

李四       数学 84

李四       物理 94

张三       语文 74

张三       数学 83

张三       物理 93

  

2、使用SQL Server 2000动态SQL

--SQL SERVER 2000动态SQL。

--调用系统表动态生态。

declare @sql varchar(8000)

select @sql=isnull(@sql+' union all ','')+' select 姓名, [课程]='

+quotename(Name,'''')+' , [分数] = '+quotename(Name)+' from tb'

from syscolumns

where Name!='姓名' and ID=object_id('tb')--表名tb,不包含列名为姓名的其他列

order by colid

exec(@sql+' order by 姓名')

3、使用SQL Server 2005静态SQL

--SQL SERVER 2005动态SQL

SELECT  姓名 ,
        课程 ,
        分数
FROM    tb UNPIVOT ( 分数 FOR 课程 IN ( [语文], [数学], [物理] ) ) t 

4、使用SQL Server 2005动态SQL

--SQL SERVER 2005动态SQL

DECLARE @sql NVARCHAR(4000)

SELECT  @sql = ISNULL(@sql + ',', '') + QUOTENAME(name)
FROM    syscolumns
WHERE   id = OBJECT_ID('tb')
        AND name NOT IN ( '姓名' )
ORDER BY colid

SET @sql = 'select 姓名,[课程],[分数] from tb unpivot ([分数] for [课程] in(' + @sql
    + '))b'

EXEC(@sql)

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website