`

行列互转

Go 
阅读更多
--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子,比较形象易懂

整理人:中国风(Roy)

日期:2008.06.06
******************************************************************************************************************************************************/

--1、行互列
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 
Go
--2000方法:
动态:

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')


生成静态:

select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end) 
from 
    Class 
group by [Student]

GO



--2005方法
动态:

declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')

生成静态:
select * 
from 
    Class 
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b

生成格式:
/*
Student 数学          物理          英语          语文
------- ----------- ----------- ----------- -----------
李四      77          85          65          65
张三      87          90          82          78

(2 行受影响)
*/

------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)

--2000方法:
动态:

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))

生成动态:

select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end),
    [总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from 
    Class 
group by [Student]

go

--2005方法:

动态:

declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a 
pivot (max([Score]) for [Course] in('+@s+'))b ')

生成静态:

select 
    [Student],[数学],[物理],[英语],[语文],[总成绩] 
from 
    (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 

生成格式:

/*
Student 数学          物理          英语          语文          总成绩
------- ----------- ----------- ----------- ----------- -----------
李四      77          85          65          65          292
张三      87          90          82          78          337

(2 行受影响)
*/

go

--2、列转行
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go

--2000:

动态:

declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序

生成静态:
select * 
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all 
select [Student],[Course]='物理',[Score]=[物理] from Class union all 
select [Student],[Course]='英语',[Score]=[英语] from Class union all 
select [Student],[Course]='语文',[Score]=[语文] from Class)t 
order by [Student],[Course]

go
--2005:

动态:

declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student') 
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')

go
select 
    Student,[Course],[Score] 
from 
    Class 
unpivot 
    ([Score] for [Course] in([数学],[物理],[英语],[语文]))b

生成格式:
/*
Student Course Score
------- ------- -----------
李四      数学      77
李四      物理      85
李四      英语      65
李四      语文      65
张三      数学      87
张三      物理      90
张三      英语      82
张三      语文      78

(8 行受影响)
*/

 

分享到:
评论

相关推荐

    sql 行列互转

    sql 行列互转 方便实用,对与中级使用者很有帮助,也对有这方面需求的同学参考。

    sql行列互转

    sql行列互转,存储过程中带输入和输出参数的用法,如何将exec执行结果放入变量中

    C#读写Excel表格,行列互转并输出

    本文将详细讲解如何使用C#读取Excel表格,并进行行列互转,最后将转换后的数据输出到新的Excel文件中。我们将基于Visual Studio 2012进行开发,但这些方法同样适用于其他版本的Visual Studio。 首先,你需要安装一...

    SQLServer行列互转实现思路(聚合函数)

    在SQL Server中,行列互转是一种常见的数据处理需求,它能帮助我们以更直观的方式展示数据。本篇文章将深入探讨如何使用聚合函数Pivot和Unpivot来实现这一目标,特别是针对SQL Server数据库。 首先,让我们了解什么...

    Sql行列互转

    ### SQL Server中的行列互转 #### 一、背景介绍 在处理数据时,我们经常需要对表格中的数据进行各种各样的操作,其中之一就是行列互转。比如,原始数据可能是按学生姓名和科目存储成绩,但我们需要按科目汇总所有...

    行列转换的小工具 可以进行行列互转

    行列转换的小工具 可以进行行列互转,非常好用,

    kettle 行列互转、列拆分为多行、获取文件名等控件使用

    行列互转、列拆分为多行、获取文件名(通配符获取多个路径下的文本文件输入到输出) 行列互转、列拆分为多行、获取文件名(通配符获取多个路径下的文本文件输入到输出) 行列互转、列拆分为多行、获取文件名...

    行列互转工具

    行列互换工具是网络优化中常用的小工具,希望可以方便大家的使用,使用方法简单,转化速度极快。谢谢!

    sqlserver 行列互转实现小结

    在SQL Server中,行列互转是数据处理中常见的需求,尤其在数据分析和报表展示时。这个过程通常称为“透视”或“转置”。这里主要介绍两种方法:静态方法和动态方法,以学生成绩为例进行说明。 1. **静态方法**: ...

    SQL行转列和列转行-静态互转-动态互转

    有case when方式和2005之后的内置pivot和unpivot方法来实现,行列互转,可以分为静态互转,动态互转。

    简单有用的SQL脚本 (行列互转,查询一个表内相同纪录等)

    行列互转 代码如下: create table test(id int,name varchar(20),quarter int,profile int) insert into test values(1,’a’,1,1000) insert into test values(1,’a’,2,2000) insert into test values(1,’a’,3,...

    SQL行转列和列转行代码详解

    行列互转,是一个经常遇到的需求。实现的方法,有case when方式和2005之后的内置pivot和unpivot方法来实现。 在读了技术内幕那一节后,虽说这些解决方案早就用过了,却没有系统性的认识和总结过。为了加深认识,再...

    SQL之行列互变问题

    ### SQL之行列互变问题详解 #### 一、引言 在处理数据库中的数据时,我们经常遇到需要将数据从一种格式转换为另一种格式的需求,其中最常见的一种就是行列互换的问题。例如,原始数据是按照列的形式存储的,而我们...

    批量标准矢量shp互转txt工具

    标题中的“批量标准矢量shp互转txt工具”指的是一个专为处理地理信息系统(GIS)数据而设计的软件工具,主要用于将SHP文件转换成TXT格式。SHP文件是Esri公司开发的Shapefile格式,是广泛使用的矢量地理数据存储格式...

    【ASP.NET编程知识】.net数据库操作框架SqlSugar的简单入门.docx

    SqlSugar的新功能包括配置查询、多租户+仓储+自动分配、行列互转功能等。其中,配置查询解决了大量字典表和简单就为取一个name就要写联表的问题,让单表查询解决一切。多租户+仓储+自动分配功能可以实现多租户模式下...

    易语言高级表格与EXCEL互转模块

    而“易语言高级表格与EXCEL互转模块”正是针对这种需求开发的工具,它实现了易语言程序与Microsoft Excel之间的数据交互。 这个模块的核心功能在于两个方面:一是将易语言中的高级表格数据导出为Excel文件,二是将...

    大数据生态全景图谱.xmind.docx

    数据分析主要包括多维分析、行列互转、json 处理、执行方案等几个方面。数据分析需要考虑到数据的结构、数据的关系、数据的约束等因素。 七、数据可视化/BI 数据可视化是指对数据的可视化和展示过程。数据可视化...

Global site tag (gtag.js) - Google Analytics