sql行转列动态,oracle动态列

2020-11-11 阅读 何阳夏整理

内容简介:mysql动态行列转换1、 本文的内容本文的内容背景(内容)实现SQL代码的方法有:使用拼接SQL,静态列字段;使用拼接SQL,动态列字段;使用Poracle动态行转列函数...

一.本文所涉及的内容(Contents)

本文所涉及的内容(Contents)

背景(Contexts)

实现代码(SQLCodes)

方法一:使用拼接SQL,静态列字段;

方法二:使用拼接SQL,动态列字段;

方法三:使用PIVOT关系运算符,静态列字段;

方法四:使用PIVOT关系运算符,动态列字段;

二.背景(Contexts)

其实行转列并不是一个什么新鲜的话题了,甚至已经被大家说到烂了,网上的很多例子多多少少都有些问题,所以我希望能让大家快速的看到执行的效果,所以在动态列的基础上再把表、分组字段、行转列字段、值这四个行转列固定需要的值变成真正意义的参数化,大家只需要根据自己的环境,设置参数值,马上就能看到效果了(可以直接跳转至:参数化动态PIVOT行转列查看具体的脚本代码)。

行转列的效果图如图1所示:

(图1:行转列效果图)

三.实现代码(SQLCodes)

(一)首先我们先创建一个测试表,往里面插入测试数据,返回表记录如图2所示:

--创建测试表

IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[TestRows2Columns]')ANDtypein(N'U'))

DROPTABLE[dbo].[TestRows2Columns]

GO

CREATETABLE[dbo].[TestRows2Columns](

[Id][int]IDENTITY(1,1)NOTNULL,

[UserName][nvarchar](50)NULL,

[Subject][nvarchar](50)NULL,

[Source][numeric](18,0)NULL

)ON[PRIMARY]

GO

--插入测试数据

INSERTINTO[TestRows2Columns]([UserName],[Subject],[Source])

SELECTN'张三',N'语文',60UNIONALL

SELECTN'李四',N'数学',70UNIONALL

SELECTN'王五',N'英语',80UNIONALL

SELECTN'王五',N'数学',75UNIONALL

SELECTN'王五',N'语文',57UNIONALL

SELECTN'李四',N'语文',80UNIONALL

SELECTN'张三',N'英语',100

GO

SELECT*FROM[TestRows2Columns]

(图2:样本数据)

(二)先以静态的方式实现行转列,效果如图3所示:

--1:静态拼接行转列

SELECT[UserName],

SUM(CASE[Subject]WHEN'数学'THEN[Source]ELSE0END)AS'[数学]',

SUM(CASE[Subject]WHEN'英语'THEN[Source]ELSE0END)AS'[英语]',

SUM(CASE[Subject]WHEN'语文'THEN[Source]ELSE0END)AS'[语文]'

FROM[TestRows2Columns]

GROUPBY[UserName]

GO

(图3:样本数据)

(三)接着以动态的方式实现行转列,这是使用拼接SQL的方式实现的,所以它适用于SQLServer2000以上的数据库版本,执行脚本返回的结果如图2所示;

--2:动态拼接行转列

DECLARE@sqlVARCHAR(8000)

SET@sql='SELECT[UserName],'

SELECT@sql=@sql'SUM(CASE[Subject]WHEN'''[Subject]'''THEN[Source]ELSE0END)AS'''QUOTENAME([Subject])''','

FROM(SELECTDISTINCT[Subject]FROM[TestRows2Columns])ASa

SELECT@sql=LEFT(@sql,LEN(@sql)-1)'FROM[TestRows2Columns]GROUPBY[UserName]'

PRINT(@sql)

EXEC(@sql)

GO

(四)在SQLServer2005之后有了一个专门的PIVOT和UNPIVOT关系运算符做行列之间的转换,下面是静态的方式实现的,实现效果如图4所示:

--3:静态PIVOT行转列

SELECT*

FROM(SELECT[UserName],

[Subject],

[Source]

FROM[TestRows2Columns]

)pPIVOT

(SUM([Source])FOR[Subject]IN([数学],[英语],[语文]))ASpvt

ORDERBYpvt.[UserName];

GO

(图4)

(五)把上面静态的SQL基础上进行修改,这样就不用理会记录里面存储了什么,需要转成什么列名的问题了,脚本如下,效果如图4所示:

--4:动态PIVOT行转列

DECLARE@sql_strVARCHAR(8000)

DECLARE@sql_colVARCHAR(8000)

SELECT@sql_col=ISNULL(@sql_col',','')QUOTENAME([Subject])FROM[TestRows2Columns]GROUPBY[Subject]

SET@sql_str='

SELECT*FROM(

SELECT[UserName],[Subject],[Source]FROM[TestRows2Columns])pPIVOT

(SUM([Source])FOR[Subject]IN('@sql_col'))ASpvt

ORDERBYpvt.[UserName]'

PRINT(@sql_str)

EXEC(@sql_str)

(六)也许很多人到了上面一步就够了,但是你会发现,当别人拿到你的代码,需要不断的修改成他自己环境中表名、分组列、行转列字段、字段值这几个参数,逻辑如图5所示,所以,我继续对上面的脚本进行修改,你只要设置自己的参数就可以实现行转列了,效果如图4所示:

--5:参数化动态PIVOT行转列

--=============================================

--Author:听风吹雨

--Createdate:2014.05.26

--Description:参数化动态PIVOT行转列

--Blog:

--=============================================

DECLARE@sql_strNVARCHAR(MAX)

DECLARE@sql_colNVARCHAR(MAX)

DECLARE@tableNameSYSNAME--行转列表

DECLARE@groupColumnSYSNAME--分组字段

DECLARE@row2columnSYSNAME--行变列的字段

DECLARE@row2columnValueSYSNAME--行变列值的字段

SET@tableName='TestRows2Columns'

SET@groupColumn='UserName'

SET@row2column='Subject'

SET@row2columnValue='Source'

--从行数据中获取可能存在的列

SET@sql_str=N'

SELECT@sql_col_out=ISNULL(@sql_col_out'','','''')QUOTENAME(['@row2column'])

FROM['@tableName']GROUPBY['@row2column']'

--PRINT@sql_str

EXECsp_executesql@sql_str,N'@sql_col_outNVARCHAR(MAX)OUTPUT',@sql_col_out=@sql_colOUTPUT

--PRINT@sql_col

SET@sql_str=N'

SELECT*FROM(

SELECT['@groupColumn'],['@row2column'],['@row2columnValue']FROM['@tableName'])pPIVOT

(SUM(['@row2columnValue'])FOR['@row2column']IN('@sql_col'))ASpvt

ORDERBYpvt.['@groupColumn']'

--PRINT(@sql_str)

EXEC(@sql_str)

(图5)

(七)在实际的运用中,我经常遇到需要对基础表的数据进行筛选后再进行行转列,那么下面的脚本将满足你这个需求,效果如图6所示:

--6:带条件查询的参数化动态PIVOT行转列

--=============================================

--Author:听风吹雨

--Createdate:2014.05.26

--Description:参数化动态PIVOT行转列,带条件查询的参数化动态PIVOT行转列

--Blog:

--=============================================

DECLARE@sql_strNVARCHAR(MAX)

DECLARE@sql_colNVARCHAR(MAX)

DECLARE@sql_whereNVARCHAR(MAX)

DECLARE@tableNameSYSNAME--行转列表

DECLARE@groupColumnSYSNAME--分组字段

DECLARE@row2columnSYSNAME--行变列的字段

DECLARE@row2columnValueSYSNAME--行变列值的字段

SET@tableName='TestRows2Columns'

SET@groupColumn='UserName'

SET@row2column='Subject'

SET@row2columnValue='Source'

SET@sql_where='WHEREUserName=''王五'''

--从行数据中获取可能存在的列

SET@sql_str=N'

SELECT@sql_col_out=ISNULL(@sql_col_out'','','''')QUOTENAME(['@row2column'])

FROM['@tableName']'@sql_where'GROUPBY['@row2column']'

--PRINT@sql_str

EXECsp_executesql@sql_str,N'@sql_col_outNVARCHAR(MAX)OUTPUT',@sql_col_out=@sql_colOUTPUT

--PRINT@sql_col

SET@sql_str=N'

SELECT*FROM(

SELECT['@groupColumn'],['@row2column'],['@row2columnValue']FROM['@tableName']'@sql_where')pPIVOT

(SUM(['@row2columnValue'])FOR['@row2column']IN('@sql_col'))ASpvt

ORDERBYpvt.['@groupColumn']'

--PRINT(@sql_str)

EXEC(@sql_str)

(图6)

作者给您推荐的内容
  1. 今天小编要和大家分享的是如何使用Excel中的定位功能实现快速填充,希望能够帮助到大家。01、首先在我们的电脑桌面上新建一个excel表格并点击它,如下图所示。02、然后在表格当中随便输入一些...

  2. win7激活产品密钥当我们安装一个新的Windows7系统时,我们需要用一把钥匙激活系统。今天,windows 7的编辑将给网民带来巨大的利益。福利是win7深度激活的关键。实际上win7激活...

  3. 今天小编要和大家分享的是怎么看苹果6s生产日期,希望能够帮助到大家。01、首先在我们的电脑桌面上找到爱思助手并点击它,如下图所示。02、然后根据软件提示将我们的手机和电脑用数据线连...

  4. 支付宝是一款付款软件,同时也是一款理财软件,支付宝有一个口令红包的功能,那么口令红包是什么呢?应该如何输入口令抢红包呢?小编来教教大家,一起来看看吧!01、口令红包是一种通过指...

  5. 电脑ip地址在哪看如何绑定局域网中计算机的IP地址:右键单击计算机右侧任务栏中的网络图标。如果计算机没有网络图标,可以单击“开始”、“控制面板”、“网络和共享中心”、更...

  6. 每个人都有属于自己的QQ头像,QQ群也是一样,可以由群主或管理对QQ群头像进行设置,那么如何才能替换QQ群头像呢?下面小编就来给大家讲解一下。01、首先,登录腾讯QQ,进入“联系人”界面,...

  7. Web服务器压力测试工具ab是Apache超文本传输协议(HTTP)的性能测试工具。其设计意图是描绘当前所安装的Apache的执行性能,主要是显示你安装的Apache每秒可以处理多少个云服务器压力测试...

  8. 大家好,小编在这里教大家如何设置手机QQ空间动态图片,希望能帮助到大家01、首先,大家在桌面上找到“QQ”并点击打开登陆自己的账号02、接下来,大家点击“动态”主页面再进入“好友动态”...

  9. 想设置“干啥想偷看手机”滚动态壁纸?今天就来教你如何设置。01、首先下载“动态壁纸”APP,然后打开APP。02、打开之后,在主页面中找到“搜索”并单击。03、然后在对话框中输入...

  10. 保持语言信息和不管我们在编程中多么小心,错误是不可避免的。这些错误常常使PHP编译器感到困惑。如果开发人员无法理解编译器错误消息的含义,这些错误消息不仅毫无用处,而且...