动态行列转换(列数不固定)

合集下载

PIVOT运算符使用（动态行转列）

PIVOT运算符使⽤（动态⾏转列）PIVOT运算符⽤于在列和⾏之间对数据进⾏旋转或透视转换，同时执⾏聚合运算官⽅的语法如下：PIVOT(聚合函数（value_column）FOR pivot_columnIN(<column_list>))我这边有⼀个保存⽹站下所有⼦域名PV的表PV_Test，数据如下现在要查询每个域名下不同⽇期的PVSQL代码如下DECLARE--保存最终执⾏的SQL语句@strSql nvarchar(1000),--定义变量保存要转化为列的数据@Date nvarchar(500)--赋值很重要，不赋值执⾏没结果SET@Date=''--赋值把所有要转化为列的数据保存在字符串中，并且以逗号分隔SELECT@Date=@Date+CDate FROM (SELECT DISTINCT'['+CONVERT(varchar(10), CDate, 23)+'],'AS CDate FROM PV_Test) AS T1--打印@DatePRINT(@Date)--去掉末尾的⼀个逗号SET@Date=SUBSTRING(@Date,1,LEN(@Date)-1)--使⽤PIVOT运算符转化SET@strSql='SELECT * FROM (SELECT Domain,CONVERT(varchar(10), CDate, 23) AS RQ,PV FROM dbo.PV_Test) AS a PIVOT(SUM(PV) FOR RQ IN ('+@Date+') ) AS b' --打印最终执⾏的SQLPRINT(@strSql)--执⾏sqlEXEC (@strSql)备注已经写得很清楚了，执⾏结果如下可以清楚的看到每个域名下各个⽇期的PV情况。

SQL Server 动态行转列(参数化表名、分组列、行转列字段、字段值)

一.本文所涉及的内容（Contents）本文所涉及的内容（Contents）背景（Contexts）实现代码（SQL Codes）方法一：使用拼接SQL，静态列字段；方法二：使用拼接SQL，动态列字段；方法三：使用PIVOT关系运算符，静态列字段；方法四：使用PIVOT关系运算符，动态列字段；扩展阅读一：参数化表名、分组列、行转列字段、字段值；扩展阅读二：在前面的基础上加入条件过滤；二.背景（Contexts）其实行转列并不是一个什么新鲜的话题了，甚至已经被大家说到烂了，网上的很多例子多多少少都有些问题，所以我希望能让大家快速的看到执行的效果，所以在动态列的基础上再把表、分组字段、行转列字段、值这四个行转列固定需要的值变成真正意义的参数化，大家只需要根据自己的环境，设置参数值，马上就能看到效果了。

行转列的效果图如图1所示：（图1：行转列效果图）(一) 首先我们先创建一个测试表，往里面插入测试数据，返回表记录如图2所示：--创建测试表IF EXISTS (SELECT*FROM sys.objects WHERE object_id=OBJECT_ID(N'[dbo].[TestRows2Columns]') AND type in (N'U'))DROP TABLE[dbo].[TestRows2Columns]GOCREATE TABLE[dbo].[TestRows2Columns]([Id][int]IDENTITY(1,1) NOT NULL,[UserName][nvarchar](50) NULL,[Subject][nvarchar](50) NULL,[Source][numeric](18, 0) NULL) ON[PRIMARY]GO--插入测试数据INSERT INTO[TestRows2Columns] ([UserName],[Subject],[Source])SELECT N'张三',N'语文',60UNION ALLSELECT N'李四',N'数学',70UNION ALLSELECT N'王五',N'英语',80UNION ALLSELECT N'王五',N'数学',75UNION ALLSELECT N'王五',N'语文',57UNION ALLSELECT N'李四',N'语文',80UNION ALLSELECT N'张三',N'英语',100GOSELECT*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]GROUP BY[UserName]GO（图3：样本数据）(三) 接着以动态的方式实现行转列，这是使用拼接SQL的方式实现的，所以它适用于SQL Server 2000以上的数据库版本，执行脚本返回的结果如图2所示；--2：动态拼接行转列DECLARE@sql VARCHAR(8000)SET@sql='SELECT [UserName],'SELECT@sql=@sql+'SUM(CASE [Subject] WHEN '''+[Subject]+''' THEN [Source] ELSE 0 END) AS '''+QUOTENAME([Subject])+''','FROM (SELECT DISTINCT[Subject]FROM[TestRows2Columns]) AS aSELECT@sql=LEFT(@sql,LEN(@sql)-1) +' FROM [TestRows2Columns] GROUP BY [UserName]'PRINT(@sql)EXEC(@sql)GO(四) 在SQL Server 2005之后有了一个专门的PIVOT 和UNPIVOT 关系运算符做行列之间的转换，下面是静态的方式实现的，实现效果如图4所示：--3：静态PIVOT行转列SELECT*FROM ( SELECT[UserName] ,[Subject] ,[Source]FROM[TestRows2Columns]) p PIVOT( SUM([Source]) FOR[Subject]IN ( [数学],[英语],[语文] ) ) AS pvtORDER BY pvt.[UserName];GO（图4）(五) 把上面静态的SQL基础上进行修改，这样就不用理会记录里面存储了什么，需要转成什么列名的问题了，脚本如下，效果如图4所示：--4：动态PIVOT行转列DECLARE@sql_str VARCHAR(8000)DECLARE@sql_col VARCHAR(8000)SELECT@sql_col=ISNULL(@sql_col+',','') +QUOTENAME([Subject]) FROM[TestRows2Columns]GROUP BY[Subject]SET@sql_str='SELECT * FROM (SELECT [UserName],[Subject],[Source] FROM [TestRows2Columns]) p PIVOT(SUM([Source]) FOR [Subject] IN ( '+@sql_col+') ) AS pvtORDER BY pvt.[UserName]'PRINT (@sql_str)EXEC (@sql_str)(六) 也许很多人到了上面一步就够了，但是你会发现，当别人拿到你的代码，需要不断的修改成他自己环境中表名、分组列、行转列字段、字段值这几个参数，逻辑如图5所示，所以，我继续对上面的脚本进行修改，你只要设置自己的参数就可以实现行转列了，效果如图4所示：--5：参数化动态PIVOT行转列-- =============================================-- Author: <听风吹雨>-- Create date: <2014.05.26>-- Description: <参数化动态PIVOT行转列>-- Blog: <:///gaizai/>-- =============================================DECLARE@sql_str NVARCHAR(MAX)DECLARE@sql_col NVARCHAR(MAX)DECLARE@tableName SYSNAME --行转列表DECLARE@groupColumn SYSNAME --分组字段DECLARE@row2column SYSNAME --行变列的字段DECLARE@row2columnValue SYSNAME --行变列值的字段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+'] GROUP BY ['+@row2column+']'--PRINT @sql_strEXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT --PRINT @sql_colSET@sql_str= N'SELECT * FROM (SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM ['+@tableName+']) p PIVOT(SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+@sql_col+') ) AS pvt ORDER BY pvt.['+@groupColumn+']'--PRINT (@sql_str)EXEC (@sql_str)（图5）(七) 在实际的运用中，我经常遇到需要对基础表的数据进行筛选后再进行行转列，那么下面的脚本将满足你这个需求，效果如图6所示：--6：带条件查询的参数化动态PIVOT行转列-- =============================================-- Author: <听风吹雨>-- Create date: <2014.05.26>-- Description: <参数化动态PIVOT行转列，带条件查询的参数化动态PIVOT行转列>-- Blog: <:///gaizai/>-- =============================================DECLARE@sql_str NVARCHAR(MAX)DECLARE@sql_col NVARCHAR(MAX)DECLARE@sql_where NVARCHAR(MAX)DECLARE@tableName SYSNAME --行转列表DECLARE@groupColumn SYSNAME --分组字段DECLARE@row2column SYSNAME --行变列的字段DECLARE@row2columnValue SYSNAME --行变列值的字段SET@tableName='TestRows2Columns'SET@groupColumn='UserName'SET@row2column='Subject'SET@row2columnValue='Source'SET@sql_where='WHERE UserName = ''王五'''--从行数据中获取可能存在的列SET@sql_str= N'SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])FROM ['+@tableName+'] '+@sql_where+' GROUP BY ['+@row2column+']'--PRINT @sql_strEXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT --PRINT @sql_colSET@sql_str= N'SELECT * FROM (SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM['+@tableName+']'+@sql_where+') p PIVOT(SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+@sql_col+') ) AS pvt ORDER BY pvt.['+@groupColumn+']'--PRINT (@sql_str)EXEC (@sql_str)（图6）。

Excel怎么设置行列转置

Excel怎么设置行列转置
推荐文章
怎么设置Excle的权限？热度：excel表格怎么设置文字自动换行热度：在Excel表格中数据转置怎么使用热度：怎么将Excel2003单元格行列数据转换热度： Excel文档怎么设置安全密码热度：在excel表格中，想要将行数据和列数据进行转置，应该怎么设置呢?下面随店铺一起来看看吧。

Excel设置行列转置的步骤
打开要换行的表格，选择要换行的单元格，点击右键，选择“设置单元格格式”。

在弹出的对话框中，点开“对齐”选项，勾选“自动换行”在点击确定。

可以看到要换行的单元格被自动换行了。

可以把列换成行，也可以把行换成列。

gaussdb 行转列函数

GaussDB（高斯数据库）是一款面向企业级应用场景的关系型数据库管理系统，具有高性能、高可靠性和高可扩展性等优势。

在实际的数据处理过程中，有时候需要对行数据进行转置，即将行数据转换为列数据。

为了实现这一功能，GaussDB提供了行转列函数，能够在数据库中轻松实现行列转换操作。

在本文中，我将向大家介绍GaussDB中的行转列函数，包括其基本概念、使用方法和实际应用场景等方面的内容，希望能够帮助读者更加深入地了解和使用GaussDB数据库。

一、行转列函数的基本概念行转列函数是GaussDB数据库中的一种特殊函数，用于将行数据转换为列数据。

通过行转列函数，用户可以按照自己的需求对数据库中的行数据进行重新组织和排列，从而更好地满足实际的数据处理需求。

在GaussDB中，行转列函数通常包括两个基本要素：转置的行列数据和转置的列标识。

通过指定这两个要素，用户可以灵活地对数据库中的数据进行转置操作，实现行列数据的重新排列和展示。

二、行转列函数的使用方法1. 行转列函数的语法在GaussDB中，行转列函数的基本语法如下所示：```SELECT * FROM 表名PIVOT(聚合函数(列名)FOR 列标识 IN (列值1, 列值2, 列值3, ...)) AS 别名```在上述语法中，用户需要指定要进行转置操作的表名、聚合函数、列名、列标识和列值等参数，以便数据库系统可以正确地进行行列转换操作。

2. 行转列函数的实际应用下面通过一个具体的示例来说明行转列函数的实际应用方法。

假设有一个销售数据表sales，其中包括产品名称、销售日期和销售额等字段，数据如下表所示：产品名称销售日期销售额A 2020-01-01 1000B 2020-01-01 1500A 2020-01-02 2000B 2020-01-02 2500如果需要将上述表中的销售数据按照产品名称进行转置，并求出每个产品在不同日期的销售总额，可以使用行转列函数来实现，具体的SQL语句如下所示：```SELECT * FROM salesPIVOT(SUM(销售额)FOR 销售日期 IN ('2020-01-01', '2020-01-02')) AS 销售统计```通过上述SQL语句，数据库系统将会按照产品名称进行转置操作，并求出每个产品在不同日期的销售总额，最终得到的结果如下所示：产品名称2020-01-01 2020-01-02A 1000 2000B 1500 2500通过以上示例，可以看出行转列函数在实际数据处理中的重要作用，能够帮助用户更加灵活地对数据库中的行数据进行重新组织和排列。

使用动态SQL语句实现简单的行列转置(动态产生列)

使用动态SQL语句实现简单的行列转置（动态产生列）要实现简单的行列转置，并动态产生列，可以使用动态SQL语句来实现。

首先，假设有一个表格`table1`，有`id`、`name`和`value`三个字段，我们要将`name`字段的值转换为列名，并将`value`字段的值填充到相应的位置。

动态SQL语句的实现步骤如下：
1. 使用`GROUP_CONCAT`函数将`name`字段的值连接成一个字符串，作为动态列名。

2.使用`CONCAT`函数拼接SQL语句，动态生成列的部分。

3. 使用`GROUP BY`子句将数据按照`id`字段进行分组。

4.使用动态生成的SQL语句进行查询。

下面是实现的示例代码：
```sql
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(name = "', name, '", value, NULL)) AS "', name, '"'))
FROM table1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
```
以上代码将会将`name`字段的值转换为列名，并将`value`字段的值填充到相应的位置，然后按照`id`字段进行分组，返回转置后的结果。

需要注意的是，动态SQL语句的生成需要使用`PREPARE`和`EXECUTE`语句，并在使用完毕后使用`DEALLOCATEPREPARE`释放资源。

SQLserver动态行转列

SQLserver动态⾏转列在学习数据库的时候，遇到了怎么把数据表中的内容转变成数据表的字段，在此，记录⼀下⾃⼰对⾏转列的理解⽐如有个学⽣成绩表：stuid:学号course:科⽬score：成绩表的内容为：stuid course score0101 语⽂ 780101 数学 900101 英语 670101 物理 88⽽我们想要的是类似下表：stuid 语⽂数学英语物理0101 78 90 67 88这个时候就需要⽤到⾏转列，⾏转列有动态与静态之分：静态⾏转列：通过sql语句，静态的进⾏转换，⼀旦原表的数据有改动，⽐如增加化学成绩，或者删除物理成绩，我们就得重新改变sql语句：就上表，转换的sql语句为：select stuid,max(case course when'语⽂'then score else0end)语⽂,max(case course when'数学'then score else0end)数学,max(case course when'英语'then score else0end)英语,max(case course when'物理'then score else0end)物理from scores --表名group by stuid --分组查询主要知识：max（），case，group by 分组查询。

max（）取最⼤值。

case：我的理解是从⼏个选项中选择，⽐如：case course when '语⽂' then score else 0 end当course 为语⽂时，case返回对应的score与0当中的⼀个，在本例中，查询第⼀条数据：0101 语⽂ 78此时：course=‘语⽂’，score=78，则case返回78，当查询第⼆条数据的时候：0101 数学 90course=‘语⽂’不存在，则返回 0 （else 0 ）以此类推得：max(78,0,0,0),max（）取最⼤值，最后的数据就是 78，所谓静态，就是我们⼿动静态的获取每⼀个字段（语⽂，数学，英语，物理），⼀旦科⽬有所改变，我们就得修改sql语句，不怎么⽅便⽽动态⾏转列就可以避免这种情况，它是动态的⾃⼰根据原表中的数据，获取字段名：declare@sql varchar(8000) --申明⼀个变量 @sql，数据类型为 varchar（8000）set@sql='select stuid,' -- 使⽤ set 为@sql 赋值select@sql=@sql+'max(case course when '''+course +'''then score else 0 end)'+''''+course +''','from (select distinct course from scores) as sc --使⽤select 为@sql赋值set@sql=left(@sql,len(@sql)-1)+'from scores group by stuid'exec(@sql) --执⾏@sql注意：在sql语句中，使⽤单引号 ’ 来确定字符串的范围，如果字符串本⾝含有单引号如：‘ 姓名：‘张三’，性别：‘男’ ’，这时候需要⽤ '' ,即两个单引号来表⽰字符串本⾝的单引号。

SQL Server 动态行转列(参数化表名、分组列、行转列字段、字段值)

excel表格列转行的方法

excel表格列转行的方法一、引言在Excel表格中，我们经常需要处理各种数据，其中最常见的就是将列数据转换成行数据。

这种转换方法在数据处理和分析中非常有用，可以帮助我们更好地理解数据，并对其进行分类、筛选、排序等操作。

本文将介绍几种常用的Excel表格列转行的操作方法，帮助大家更好地完成数据整理和分析工作。

二、方法一：手动调整1. 打开Excel表格，查看需要转换的列数据。

2. 将鼠标移动到两列数据之间的分界线上，当光标变成双向箭头时，按住鼠标左键拖动分界线，将列数据分成多行显示。

3. 根据需要调整行高，使数据更加清晰易读。

三、方法二：使用“转置”功能1. 选中需要转换的列数据，点击工具栏中的“数据”选项卡。

2. 在“数据工具”组中，选择“分列”按钮。

3. 在弹出的“分列向导”中，选择“分隔符”（如果数据是由逗号、制表符等分隔符分隔的），然后点击“完成”按钮。

4. 在弹出的对话框中，勾选“转置”选项，然后点击“确定”。

5. Excel会将选中的列数据转换为行数据，并自动调整行高和列宽。

四、方法三：使用公式转换1. 选中需要转换的列数据的下一行空白的单元格。

2. 在单元格中输入公式：=——然后按下Ctrl+Shift+Enter组合键，这样就会将选中的列数据转换为行数据。

3. 根据需要调整行高和列宽。

五、注意事项1. 在使用手动调整方法时，要确保分界线放置的位置不会影响数据的准确性。

2. 在使用“转置”功能时，要确保数据分隔符的正确性，否则可能会导致转换失败或数据丢失。

3. 在使用公式转换方法时，要确保公式的正确性，否则可能会导致转换失败或数据错误。

4. 在转换过程中，要保持数据的完整性，不要删除或修改原始数据。

六、总结以上三种方法都是常用的Excel表格列转行的操作方法，可以根据实际情况选择合适的方法进行操作。

无论使用哪种方法，都要注意数据的准确性、完整性和易读性，以确保数据处理和分析工作的顺利进行。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

众所周知，静态SQL的输出结构必须也是静态的。

对于经典的行转列问题，如果行数不定导致输出的列数不定，标准的答案就是使用动态SQL, 到11G里面则有XML结果的PIVOT。

今天在asktom看到的一篇贴子彻底颠覆了我的看法！贴子里的链接指向另一个牛人辈出的荷兰公司：http://technology.amis.nl/2006/0 ... ing-antons-thunder/还记得Anton Scheffer吗？这位神人先是用10G的MODEL写了SUDOKU的一句SQL的解法，在11GR2推出之后又率先用递归WITH写了个只有短短几行的SUDOKU解法。

他的作品还有EXCEL文件生成器。

早在2006年他就发明了真正动态的行转列办法，用的是一系列神秘的函数，如同自定义聚合函数STRAGG里面用的那些。

这个神秘的对象代码如下：1.CREATE OR REPLACE2.type PivotImpl as object3.(4.ret_type anytype,-- The return type of the table function5.stmt varchar2(32767),6.fmt varchar2(32767),7.cur integer,8.static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)',dummy in number := 0 )9.return number,10.static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt invarchar2 := 'upper(@p@)', dummy in number := 0 )11.return number,12.static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)',dummy in number := 0 )13.return number,14.member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )15.return number,16.member function ODCITableClose( self in PivotImpl )17.return number18.)19./20.21.create or replace type body PivotImpl as22.static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)',dummy in number )23.return number24.is25.atyp anytype;26.cur integer;27.numcols number;28.desc_tab dbms_sql.desc_tab2;29.rc sys_refcursor;30.t_c2 varchar2(32767);31.t_fmt varchar2(1000);32.begin33.cur := dbms_sql.open_cursor;34.dbms_sql.parse( cur, p_stmt, dbms_sql.native );35.dbms_sql.describe_columns2( cur, numcols, desc_tab );36.dbms_sql.close_cursor( cur );37.--38.anytype.begincreate( dbms_types.typecode_object, atyp );39.for i in 1 .. numcols - 240.loop41.atyp.addattr( desc_tab( i ).col_name42., case desc_tab( i ).col_type43.when 1then dbms_types.typecode_varchar244.when 2then dbms_types.typecode_number45.when 9then dbms_types.typecode_varchar246.when 11then dbms_types.typecode_varchar2-- show rowid asvarchar247.when 12then dbms_types.typecode_date48.when 208 then dbms_types.typecode_varchar2-- show urowid asvarchar249.when 96then dbms_types.typecode_char50.when 180 then dbms_types.typecode_timestamp51.when 181 then dbms_types.typecode_timestamp_tz52.when 231 then dbms_types.typecode_timestamp_ltz53.when 182 then dbms_types.typecode_interval_ym54.when 183 then dbms_types.typecode_interval_ds55.end56., desc_tab( i ).col_precision57., desc_tab( i ).col_scale58., case desc_tab( i ).col_type59.when 11 then 18-- for rowid col_max_len = 16, and 18 characters areshown60.else desc_tab( i ).col_max_len61.end62., desc_tab( i ).col_charsetid63., desc_tab( i ).col_charsetform64.);65.end loop;66.if instr( p_fmt, '@p@' ) > 067.then68.t_fmt := p_fmt;69.else70.t_fmt := '@p@';71.end if;72.open rc for replace( 'select distinct ' || t_fmt || '73.from( ' || p_stmt || ' )74.order by ' || t_fmt75., '@p@'76., desc_tab( numcols - 1 ).col_name77.);78.loop79.fetch rc into t_c2;80.exit when rc%notfound;81.atyp.addattr( t_c282., case desc_tab( numcols ).col_type83.when 1then dbms_types.typecode_varchar284.when 2then dbms_types.typecode_number85.when 9then dbms_types.typecode_varchar286.when 11then dbms_types.typecode_varchar2-- show rowid asvarchar287.when 12then dbms_types.typecode_date88.when 208 then dbms_types.typecode_urowid89.when 96then dbms_types.typecode_char90.when 180 then dbms_types.typecode_timestamp91.when 181 then dbms_types.typecode_timestamp_tz92.when 231 then dbms_types.typecode_timestamp_ltz93.when 182 then dbms_types.typecode_interval_ym94.when 183 then dbms_types.typecode_interval_ds95.end96., desc_tab( numcols ).col_precision97., desc_tab( numcols ).col_scale98., case desc_tab( numcols ).col_type99.when 11 then 18-- for rowid col_max_len = 16, and 18 characters areshown100.else desc_tab( numcols ).col_max_len101.end102., desc_tab( numcols ).col_charsetid103., desc_tab( numcols ).col_charsetform104.);105.end loop;106.close rc;107.atyp.endcreate;108.anytype.begincreate( dbms_types.typecode_table, rtype );109.rtype.SetInfo( null, null, null, null, null, atyp, dbms_types.typecode_object, 0 );110.rtype.endcreate();111.return odciconst.success;112.exception113.when others then114.return odciconst.error;115.end;116.--117.static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )118.return number119.is120.prec pls_integer;121.scale pls_integer;122.len pls_integer;123.csid pls_integer;124.csfrm pls_integer;125.elem_typ anytype;126.aname varchar2(30);127.tc pls_integer;128.begin129.tc := ti.RetType.GetAttrElemInfo( 1, prec, scale, len, csid, csfrm, elem_typ, aname );130.--131.if instr( p_fmt, '@p@' ) > 0132.then133.sctx := PivotImpl( elem_typ, p_stmt, p_fmt, null );134.else135.sctx := PivotImpl( elem_typ, p_stmt, '@p@', null );136.end if;137.return odciconst.success;138.end;139.--140.static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )141.return number142.is143.cur integer;144.numcols number;145.desc_tab dbms_sql.desc_tab2;146.t_stmt varchar2(32767);147.type_code pls_integer;148.prec pls_integer;149.scale pls_integer;150.len pls_integer;151.csid pls_integer;152.csfrm pls_integer;153.schema_name varchar2(30);154.type_name varchar2(30);155.version varchar2(30);156.attr_count pls_integer;157.attr_type anytype;158.attr_name varchar2(100);159.dummy2integer;160.begin161.cur := dbms_sql.open_cursor;162.dbms_sql.parse( cur, p_stmt, dbms_sql.native );163.dbms_sql.describe_columns2( cur, numcols, desc_tab );164.dbms_sql.close_cursor( cur );165.--166.for i in 1 .. numcols - 2167.loop168.t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';169.end loop;170.--171.type_code := sctx.ret_type.getinfo( prec172., scale173., len174., csid175., csfrm176., schema_name177., type_name178., version179., attr_count180.);181.for i in numcols - 1 .. attr_count182.loop183.type_code := sctx.ret_type.getattreleminfo( i184., prec 185., scale 186., len 187., csid 188., csfrm189., attr_type 190., attr_name 191.);192.t_stmt := t_stmt || replace( ', max( decode( ' || sctx.fmt || ', ''' || attr_name || ''', ' || desc_tab( numcols ).col_name || ' ) )'193., '@p@'194., desc_tab( numcols - 1 ).col_name 195.); 196.end loop;197.t_stmt := 'select ' || substr( t_stmt, 2 ) || ' from ( ' || sctx.stmt || ' )';198.for i in 1 .. numcols - 2199.loop200.if i = 1201.then202.t_stmt := t_stmt || ' group by "' || desc_tab( i ).col_name || '"';203.else204.t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';205.end if;206.end loop;207.--208.--dbms_output.put_line( t_stmt );209.sctx.cur := dbms_sql.open_cursor;210.dbms_sql.parse( sctx.cur, t_stmt, dbms_sql.native );211.for i in 1 .. attr_count212.loop213.type_code := sctx.ret_type.getattreleminfo( i214., prec 215., scale 216., len217., csid218., csfrm 219., attr_type 220., attr_name 221.);222.case type_code223.when dbms_types.typecode_char then dbms_sql.define_column( sctx.cur, i, 'x', 32767 );224.when dbms_types.typecode_varchar2then dbms_sql.define_column( sctx.cur, i, 'x', 32767 ); 225.when dbms_types.typecode_number then dbms_sql.define_column( sctx.cur, i, cast( null as number ) );226.when dbms_types.typecode_date then dbms_sql.define_column( sctx.cur, i, cast( null as date ) );227.when dbms_types.typecode_urowid then dbms_sql.define_column( sctx.cur, i, cast( null as urowid ) );228.when dbms_types.typecode_timestamp then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp ) );229.when dbms_types.typecode_timestamp_tz then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with time zone ) );230.when dbms_types.typecode_timestamp_ltz then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with local time zone ) );231.when dbms_types.typecode_interval_ym then dbms_sql.define_column( sctx.cur, i, cast( null as interval year to month ) );232.when dbms_types.typecode_interval_ds then dbms_sql.define_column( sctx.cur, i, cast( null asinterval day to second ) );233.end case;234.end loop;235.dummy2 := dbms_sql.execute( sctx.cur );236.return odciconst.success;237.end;238.--239.member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset ) 240.return number241.is242.c1_col_type pls_integer;243.type_code pls_integer;244.prec pls_integer;245.scale pls_integer;246.len pls_integer;247.csid pls_integer;248.csfrm pls_integer;249.schema_name varchar2(30);250.type_name varchar2(30);251.version varchar2(30);252.attr_count pls_integer;253.attr_type anytype;254.attr_name varchar2(100);255.v1varchar2(32767);256.n1number;257.d1date;258.ur1urowid;259.ids1interval day to second;260.iym1interval year to month;261.ts1timestamp;262.tstz1timestamp with time zone;263.tsltz1 timestamp with local time zone;264.begin265.outset := null;266.if nrows < 1267.then268.-- is this possible???269.return odciconst.success;270.end if;271.--272.--dbms_output.put_line( 'fetch' );273.if dbms_sql.fetch_rows( self.cur ) = 0274.then275.return odciconst.success;276.end if;277.--278.--dbms_output.put_line( 'done' );279.type_code := self.ret_type.getinfo( prec280., scale281., len282., csid283., csfrm284., schema_name285., type_name286., version287., attr_count288.);289.anydataset.begincreate( dbms_types.typecode_object, self.ret_type, outset );290.outset.addinstance;291.outset.piecewise();292.for i in 1 .. attr_count293.loop294.type_code := self.ret_type.getattreleminfo( i295., prec 296., scale 297., len 298., csid 299., csfrm 300., attr_type 301., attr_name 302.);303.--dbms_output.put_line( attr_name );304.case type_code305.when dbms_types.typecode_char then306.dbms_sql.column_value( self.cur, i, v1 );307.outset.setchar( v1 );308.when dbms_types.typecode_varchar2 then309.dbms_sql.column_value( self.cur, i, v1 );310.outset.setvarchar2( v1 );311.when dbms_types.typecode_number then312.dbms_sql.column_value( self.cur, i, n1 );313.outset.setnumber( n1 );314.when dbms_types.typecode_date then315.dbms_sql.column_value( self.cur, i, d1 );316.outset.setdate( d1 );317.when dbms_types.typecode_urowid then318.dbms_sql.column_value( self.cur, i, ur1 );319.outset.seturowid( ur1 );320.when dbms_types.typecode_interval_ds then321.dbms_sql.column_value( self.cur, i, ids1 );322.323.outset.setintervalds( ids1 );324.when dbms_types.typecode_interval_ym then325.dbms_sql.column_value( self.cur, i, iym1 );326.outset.setintervalym( iym1 );327.when dbms_types.typecode_timestamp then328.dbms_sql.column_value( self.cur, i, ts1 );329.outset.settimestamp( ts1 );330.when dbms_types.typecode_timestamp_tz then331.dbms_sql.column_value( self.cur, i, tstz1 );332.outset.settimestamptz( tstz1 );333.when dbms_types.typecode_timestamp_ltz then334.dbms_sql.column_value( self.cur, i, tsltz1 );335.outset.settimestampltz( tsltz1 );336.end case;337.end loop;338.outset.endcreate;339.return odciconst.success;340.end;341.--342.member function ODCITableClose( self in PivotImpl )343.return number344.is345. c integer;346.begin347. c := self.cur;348.dbms_sql.close_cursor( c );349.return odciconst.success;350.end;351.end;352./353.354.-- 在外面包装一层PLSQL函数：355.create or replace356.function pivot( p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 ) 357.return anydataset pipelined using PivotImpl;358./下面就以SCOTT.EMP表为例子，这个表的结构为：Name Null?Type------------------ ----------------EMPNO NOT NULL NUMBER(4)ENAME VARCHAR2(10)JOB VARCHAR2(9)MGR NUMBER(4)HIREDATE DATESAL NUMBER(7,2)COMM NUMBER(7,2)DEPTNO NUMBER(2)如果要列出每个部门里每种职位的平均工资，传统的写法是这样：select deptno, avg(DECODE(job,'ANALYST',sal))AS ANALYST, avg(DECODE(job,'CLERK',sal))AS CLERK, avg(DECODE(job,'MANAGER',sal))AS MANAGER, avg(DECODE(job,'PRESIDENT',sal)) AS PRESIDENT, avg(DECODE(job,'SALESMAN',sal))AS SALESMANfrom scott.empgroup by deptno;输出：DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN------- ---------- ---------- ---------- ---------- ----------3095028501400203000950297510130024505000用这个神奇的pivot函数的写法：select *from table( pivot('select deptno,job,avg(sal) sal_avgfrom scott.empgroupby deptno,job'));输出：DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN ---------- ---------- ---------- ---------- ---------- ----------3095028501400203000950297510130024505000。

动态行列转换(列数不固定)

PIVOT运算符使用（动态行转列）

SQL Server 动态行转列(参数化表名、分组列、行转列字段、字段值)

Excel怎么设置行列转置

gaussdb 行转列 函数

使用动态SQL语句实现简单的行列转置(动态产生列)

SQLserver动态行转列

SQL Server 动态行转列(参数化表名、分组列、行转列字段、字段值)

excel表格列转行的方法

gaussdb 行转列函数