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

合集下载

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表格列转行的操作方法，可以根据实际情况选择合适的方法进行操作。

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

excel行列对调的方法

excel行列对调的方法嘿，朋友们！今天咱就来讲讲 Excel 行列对调这个事儿。

你说这Excel 啊，就像是一个神奇的魔法盒子，里面藏着好多好多的小秘密和技巧呢！想象一下，你面前有一张表格，里面的行和列就像是一群排好队的小精灵。

现在呢，我们要让这些小精灵来个乾坤大挪移，把行变成列，列变成行。

听起来是不是很有意思呀？其实方法挺简单的。

首先呢，咱得选中要对调的那些数据。

就好像是从一群小精灵里挑出我们要变魔法的那部分。

然后呢，复制它们。

这就像是给这些小精灵施了个复制魔法。

接下来，可关键啦！我们找到一个空白的地方，右键点击，在弹出的菜单里找到“选择性粘贴”。

这就像是打开了魔法的大门哦！在选择性粘贴里，有个“转置”的选项，勾选它！哇塞，就像施了魔法一样，瞬间那些行和列就调换过来啦！你看，这多神奇呀！就好像是把整个表格给翻了个身。

之前在横向排列的数据，现在都跑到纵向去啦，反之亦然。

这就好比是一群小精灵在跳一场特别的舞蹈，位置都变了呢！学会了这个方法，以后处理表格的时候可就方便多啦！你可以把那些横着不好看的数据一下子变成竖着的，让表格更加清晰明了。

比如说，你有一份学生成绩的表格，原来行是学生的名字，列是不同的科目成绩。

现在你想看看每个科目下学生成绩的分布情况，那用这个行列对调的方法，不就一下子搞定啦！是不是超级实用呢？再比如，你在做一份数据分析，数据的排列方式不太符合你的需求，这时候用这个方法，就能轻松地让数据以你想要的方式呈现出来。

哎呀呀，Excel 真的是太强大啦！这小小的行列对调方法，就能给我们带来这么大的便利。

所以呀，大家一定要好好掌握这个技巧哦，说不定什么时候就能派上大用场呢！别再犹豫啦，赶紧去试试吧！让我们的 Excel 表格变得更加精彩！。

excel行列互换的方法

excel行列互换的方法Excel是一款功能强大的电子表格软件，广泛应用于数据分析、数据处理、报表制作等工作中。

在使用Excel的过程中，有时会遇到需要行列互换的情况，即将表格中的行转换为列，将列转换为行。

本文将详细介绍如何实现Excel行列互换的方法。

一、使用转置功能实现行列互换Excel提供了一个非常方便的功能，即转置功能，可以将选定区域的行和列互换。

下面将介绍如何使用转置功能实现行列互换。

1. 首先，打开Excel并选择需要进行行列互换的数据区域。

2. 在Excel的菜单栏中，选择“编辑”选项，然后点击“剪贴板”按钮，打开剪贴板面板。

3. 在剪贴板面板中，点击“转置”按钮，即可实现选定区域的行列互换。

4. 最后，将转置后的数据粘贴到需要的位置即可完成行列互换。

二、使用公式实现行列互换除了使用转置功能进行行列互换，我们还可以使用公式来实现。

下面将介绍两种常用的公式方法。

1. 使用INDEX和MATCH函数实现行列互换INDEX函数和MATCH函数是Excel中非常常用的两个函数，它们可以结合使用来实现行列互换。

假设我们需要将A1:E5范围内的数据进行行列互换。

在F1单元格中输入以下公式：=INDEX($A$1:$E$5,MATCH(F$1,$A$1:$A$5,0),MATCH($E1,$A$ 1:$E$1,0))然后，将该公式拖动到需要的区域即可完成行列互换。

2. 使用TRANSPOSE函数实现行列互换TRANSPOSE函数是一个专门用于行列互换的函数，可以将选定区域的行和列互换。

假设我们需要将A1:E5范围内的数据进行行列互换。

在F1单元格中输入以下公式：=TRANSPOSE($A$1:$E$5)然后，按下Ctrl+Shift+Enter组合键，将该公式作为数组公式输入。

三、使用宏实现行列互换如果需要频繁进行行列互换，可以使用宏来实现自动化操作。

下面将介绍如何使用宏实现行列互换。

1. 首先，打开Excel并按下Alt+F11组合键，打开宏编辑器。

行列互换其值不变证明

行列互换其值不变证明行列互换指的是将一个矩阵的行和列互换位置，即行变为列，列变为行。

本文将证明行列互换不改变矩阵的值。

首先，我们来定义一个矩阵。

一个矩阵是由m行n列的元素组成的矩形阵列。

矩阵中每个元素的位置由两个索引表示，第一个索引代表元素所在的行，第二个索引代表元素所在的列。

我们用A[i][j]来表示矩阵A中第i行第j列的元素。

假设我们有一个矩阵A，它是由m行n列的元素组成的。

我们将行列互换得到的矩阵为B，它是由n行m列的元素组成的。

现在，我们来证明矩阵A和矩阵B具有相同的值。

首先，我们来比较矩阵A和矩阵B中相同位置的元素。

对于A中的元素A[i][j]，它在B中的位置为B[j][i]，所以A[i][j]与B[j][i]是相同的元素。

因此，矩阵A和矩阵B在相同位置上的元素是相等的。

接下来，我们来比较矩阵A和矩阵B在不同位置的元素。

假设A中的元素A[i][j]与B中的元素B[p][q]不在同一位置。

根据行列互换的定义，元素A[i][j]在B中的位置为B[j][i]。

由于元素A[i][j]不等于B[p][q]，所以A[i][j]与B[j][i]也不等于B[p][q]。

换句话说，如果矩阵A和矩阵B在不同位置的元素不相等，那么行列互换后，它们在互换后的矩阵中仍然不相等。

综上所述，我们可以得出结论：矩阵A和矩阵B具有相同的值。

也就是说，行列互换不改变矩阵的值。

下面，我们通过一个具体的例子来进一步说明行列互换不改变矩阵的值。

假设我们有一个3行2列的矩阵A，它的元素如下所示：A[1][1] = 1 A[1][2] = 2A[2][1] = 3 A[2][2] = 4A[3][1] = 5 A[3][2] = 6将矩阵A的行和列互换得到矩阵B，如下所示：B[1][1] = 1 B[1][2] = 3 B[1][3] = 5B[2][1] = 2 B[2][2] = 4 B[2][3] = 6可以看到，矩阵A和矩阵B具有相同的值。

oracle动态行转不定列

oracle----------行转列（动态行转不定列）----测试通过（9i）/*物料需要数量需要仓库现存量仓库现存量仓库数量批次A1 2 C1 C1 20 123A1 2 C1 C2 30 111A1 2 C1 C2 20 222A1 2 C1 C3 10 211A2 3 C4 C1 40 321A2 3 C4 C4 50 222A2 3 C4 C4 60 333A2 3 C4 C5 70 223我需要把上面的查询结果转换为下面的。

物料需要数量需要仓库C1 C2 C3 C4 C5A1 2 C1 20 50 10 0 0A2 3 C4 40 0 0 110 70*/---------------------------------------------------------------建表----------------判断表是否存在declare num number;beginselect count(1) into num from user_tables where table_name='TEST';if num>0 thenexecute immediate 'drop table TEST';end if;end;----------------建表CREATE TABLE TEST(WL VARCHAR2(10),XYSL INTEGER,XYCK VARCHAR2(10),XCLCK VARCHAR2(10),XCLCKSL INTEGER,PC INTEGER);----------------第一部分测试数据INSERT INTO TEST VALUES('A1', 2, 'C1', 'C1' , 20, 123);INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 30, 111);INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 20, 222);INSERT INTO TEST VALUES('A1', 2, 'C1', 'C3' , 10, 211);INSERT INTO TEST VALUES('A2', 3, 'C4', 'C1' , 40, 321);INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 50, 222);INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 60, 333);INSERT INTO TEST VALUES('A2', 3, 'C4', 'C5' , 70, 223); COMMIT;--select * from test;---------------------------------------------------------------行转列的存储过程CREATE OR REPLACE PROCEDURE P_TEST ISV_SQL VARCHAR2(2000);CURSOR CURSOR_1 IS SELECT DISTINCT T.XCLCK FROM TEST T ORDER BY XCLCK;BEGINV_SQL := 'SELECT WL,XYSL,XYCK';FOR V_XCLCK IN CURSOR_1LOOPV_SQL := V_SQL || ',' || 'SUM(DECODE(XCLCK,''' || V_XCLCK.XCLCK || ''',XCLCKSL,0)) AS ' || V_XCLCK.XCLCK;END LOOP;V_SQL := V_SQL || ' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK';--DBMS_OUTPUT.PUT_LINE(V_SQL);V_SQL := 'CREATE OR REPLACE VIEW RESULT AS '|| V_SQL;--DBMS_OUTPUT.PUT_LINE(V_SQL);EXECUTE IMMEDIATE V_SQL;END;----------------------------------------------------------------结果----------------执行存储过程，生成视图BEGINP_TEST;END;----------------结果SELECT * FROM RESULT T;WL XYSLXYCK C1 C2 C3 C4 C5---------- --------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------A1 2C1 20 50 10 0 0A2 3C4 40 0 0 110 70----------------第二部分测试数据INSERT INTO TEST VALUES('A1', 2, 'C1', 'C6' , 20, 124); INSERT INTO TEST VALUES('A2', 2, 'C1', 'C7' , 30, 121); INSERT INTO TEST VALUES('A3', 2, 'C1', 'C8' , 20, 322); COMMIT;----------------报告存储过程，生成视图BEGINP_TEST;END;----------------结果SELECT * FROM RESULT T;WL XYSLXYCK C1 C2 C3 C4 C5 C6 C7 C8 ----- ----- -------- ------- -------- ---------- ---------- ---------- ---------- ---------- ----------A1 2C1 20 50 10 0 0 20 0 0A2 2C1 0 0 0 0 0 0 30 0A2 3C4 40 0 0 110 70 0 0 0A3 2C1 0 0 0 0 0 0 0 20--------------- 删除实体DROP VIEW RESULT;DROP PROCEDURE P_TEST;DROP TABLE TEST;。

Excel表格里行和列数据交换位置的技巧(转置)

数组函数在输入的时候有些技巧。比如没有选定范围，只选定了一个单元格输入函数，按CTRL+SHIFT+ENTER后，只显示左上角一个单元格的数据。为了显示全部数据，以输入函数的单元格为左上角顶点，选择跟源区域对应的区域，按F2，再按CTRL+SHIFT+ENTER，数据就全部显示了。
2.右键单击第一个目标单元格(也就是要粘贴的数据的最左上角的单元格)，选择选择性粘贴。(也可以从菜单选择编辑—>选择性粘贴，)
3.在选择性粘贴对话框里，选择转置并点击确定。
转置选项在对话框的右下角。
二、用Transpose函数
还有一种方法是用Transpose函数。不过还是上面讲的“特殊粘贴”方法简单。
TRANSPOSE函数可以把垂直的单元格返回成水平的单元格，反之亦然，把水平的单元格返回成垂直的单元格。TRANSPOSE必须作为一个数组函数(数组函数：对一组或多组值进行计算并返回单个或多个值的函数。数组函数包含在大括号{ }里，按CTRL+SHIFT+ENTER输入。)输入。
语法：TRANSPOSE(array)
Excel表格里行和列数据交换位置的技巧
把Excel表格里行和列数据交换位置，也就是矩阵的转置(Transpose)，技巧如下：
数据示例——
上海出发到各城市的机票价格
北京
济南
青岛
沈阳
1050
900
1200
1350
想要的结果：
北京
1050
济南
900
青岛
1200
沈阳
1350
一、用菜单命令的方法
1.选择并复制要进行行列交换的单元格范围。
array可以是一个数组，也可以是表单上的单元格范围。

Oracle行转列（不固定行数的行转列，动态）（转）

Oracle⾏转列（不固定⾏数的⾏转列，动态）（转）1. Oracle 11g之后新增了⾏列转换的函数 pivot 和 unpivot⼤⼤简化了⾏列转换处理。

2. 在Oracle 10g及以前版本，⼀般是通过各种SQL进⾏⾏列转换，列⼊下⾯例⼦：create or replace procedure P_row_to_col(tabname in varchar2,group_col in varchar2,column_col in varchar2,value_col in varchar2,Aggregate_func in varchar2default'max',colorder in varchar2default null,roworder in varchar2default null,when_value_null in varchar2default null,viewname in varchar2default'v_tmp')Authid Current_User as sqlstr varchar2(2000) :='create or replace view '|| viewname ||' as select '|| group_col ||'';c1 sys_refcursor;v1 varchar2(100);beginopen c1 for'select distinct '|| column_col ||' from '|| tabname ||case when colorder isnot null then' order by '|| colorderend;loop fetch c1 into v1;exit when c1%notfound;sqlstr := sqlstr || chr(10) ||','||case when when_value_null isnot null then'nvl('end|| Aggregate_func ||'(decode(to_char('|| column_col ||'),'''|| v1 ||''','|| value_col ||'))'||case when when_value_null isnot null then chr(44) || when_value_null || chr(41)end||'"'|| v1 ||'"';end loop;close c1;sqlstr := sqlstr ||' from '|| tabname ||' group by '|| group_col ||case when roworder isnot null then' order by '|| roworderend;execute immediate sqlstr;end P_row_to_col;--测试数据create table rowtocol_test asselect2009year,1month,'AAA1' dept,50000 expenditure from dualunion all select2009,2,'AAA1',20000from dualunion all select2009,2,'AAA1',30000from dualunion all select2010,1,'AAA1',35000from dualunion all select2009,2,'BBB2',40000from dualunion all select2009,3,'BBB2',25000from dualunion all select2010,2,'DDD3',60000from dualunion all select2009,2,'DDD3',15000from dualunion all select2009,2,'DDD3',10000from dual;select*from rowtocol_test;--执⾏测试beginp_row_to_col('rowtocol_test','year,month','dept','expenditure',Aggregate_func =>'sum',colorder =>'dept',roworder =>'1,2',when_value_null =>'0');end;select*from v_tmp;================================================================================================================例⼦⼆三：--测试数据create table t (XH varchar2(10), DDATE date, SXF int);insert into t select1,sysdate,10from dualunion all select1,sysdate+1,14from dualunion allselect 1,sysdate+2,23from dualunion allselect 2,sysdate,21from dualunion allselect 2,sysdate+1,24from dualunion allselect 3,sysdate,13from dualunion allselect 3,sysdate+1,22from dual;--create or replace package sp_test istype ResultData is ref cursor;procedure getRstData( rst out ResultData); end sp_test;/create or replace package body sp_test isprocedure getRstData( rst out ResultData)is begindeclare cursor cur is select distinct (DDATE) from t;tmp_ddate date;str varchar2(4000);beginstr:='select xh';open cur;loop fetch cur into tmp_ddate;exit when cur%notfound;str:=str||',sum(decode(to_char(ddate,''yyyymmdd''),'||chr(39)||to_char(tmp_ddate,'yyyymmdd')||chr(39)||',sxf,0)) "'||to_char(tmp_ddate,'yyyymmdd')||'"'; end loop;str:=str||' from t group by xh';--dbms_output.put_line(str);close cur;open rst for str; end;end;end sp_test;/--输出结果1101423221240313220========================例⼦三：------------建表CREATE TABLE TEST(WL VARCHAR2(10),XYSL INTEGER,XYCK VARCHAR2(10),XCLCK VARCHAR2(10),XCLCKSL INTEGER,PC INTEGER); ------------ 第⼀部分测试数据INSERT INTO TEST VALUES('A1', 2, 'C1', 'C1' , 20, 123);INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 30, 111);INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 20, 222);INSERT INTO TEST VALUES('A1', 2, 'C1', 'C3' , 10, 211);INSERT INTO TEST VALUES('A2', 3, 'C4', 'C1' , 40, 321);INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 50, 222);INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 60, 333);INSERT INTO TEST VALUES('A2', 3, 'C4', 'C5' , 70, 223);COMMIT;-------------------- 动态⽣成结果表DECLARE V_SQL VARCHAR2(2000);CURSOR CURSOR_1 IS SELECT DISTINCT T.XCLCK FROM TEST T ORDER BY XCLCK;BEGIN V_SQL :='SELECT WL,XYSL,XYCK';FOR V_XCLCK IN CURSOR_1LOOP V_SQL := V_SQL ||','||'SUM(DECODE(XCLCK,'''|| V_XCLCK.XCLCK ||''',XCLCKSL,0)) AS '|| V_XCLCK.XCLCK;END LOOP;V_SQL := V_SQL ||' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK';--DBMS_OUTPUT.PUT_LINE(V_SQL);V_SQL :='CREATE TABLE RESULT AS '|| V_SQL;--DBMS_OUTPUT.PUT_LINE(V_SQL);EXECUTE IMMEDIATE V_SQL;END;--------------- 结果SELECT*FROM RESULT T;。

oracle动态行变列

ORACLE动态列及高级查询
在开发过程中，许多时候为了能美观简洁的显示出所要的数据，经常要用到一个高级查询和动态行、列的查询语句。

一、在oracle中的分页操作，可以通过控制查询出来的行数来控制每页要显示的数据。

子查询先查出前面的十条数据，然后通过父查询筛选出其中的后六条数据。

其中子查询中的rownum必须要取别名供父查询使用，但是子查询中的where不能使用其别名。

二、经典列变行。

子查询只能查询出改变前每行中的一个属性（如s.stu_name）来做为查改变后的一列，父查询必须是伪表，不能使用任何实表。

改变后的列名通过as可以任意取。

三、jsp和oracle中的一些隐式对象和伪表伪列。

四、直接用between…and…做日期对比的筛选。

五、通过to_char把日期类型改变为字符类型，然后做对比，提高效率。

总结：动态行列都是把每一个父查询又当作子查询，通过0 as a为要改变的行空出列。

好好学习天天向上。

动态改变表格的行数列数（添加表格）

动态改变表格的⾏数列数（添加表格）1<%@ Page Language="C#" %>23<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "/TR/xhtml1/DTD/xhtml1-transitional.dtd">45<script runat="server">67protected void Page_Load(object sender, EventArgs e)8 {910 }1112protected void Button1_Click(object sender, EventArgs e)13 {14int rn = int.Parse(rows.SelectedValue);//得到需要增加的⾏数15int cn = int.Parse(cell.SelectedValue);//得到需要增加的列数1617for (int i = 1; i <= rn; i++)//18 {//进⼊外部循环，也就是循环增加表⾏19 TableRow tr = new TableRow();//申明⼀个表格⾏20for (int j = 1; j <= cn; j++)21 {//进⼊内部循环，以增加表格列22 TableCell tc = new TableCell();//申明⼀个表格列23if (i == 1)24 {//如果是第⼀⾏，就增加下⾯的字25 tc.Controls.Add(new LiteralControl("洪川"));26 }27else28 {//否则29 tc.Controls.Add(new LiteralControl(i.ToString() + "," + j.ToString()));//在表格⾥增加当前是⾏，列坐标30 }31 tr.Cells.Add(tc);//把列增加到⾏⾥⾯去32 }33 Table1.Rows.Add(tr);//把⾏增加到表⾥去34 }35 }36</script>3738<html xmlns="/1999/xhtml" >39<head runat="server">40 <title>⽆标题页</title>41</head>42<body>43 <form id="form1" runat="server">44 <div>45 Table ,这个有点HTML基础的⼈都知道了,是表格的意思啦,也是布局的⼀个重要⽅法,如果是⽤DW的话,你就会知道他有多重要了!<br /> 46⽽VS推出的Table服务器控件最⼤的特⾊当推他可以动态是控制表格的⾏列数,下⾯还是做个演⽰:<br />47 <br />48 <strong>49演⽰⼀: 动态添加表格⾏和列，在特定的格⾥写特定的字，并在当前单元格⾥⾯把坐标写出来</strong><br />50 <asp:Table ID="Table1" runat="server" BorderColor="Black" BorderStyle="Dashed" BorderWidth="1px" GridLines="Both">51 </asp:Table>52 <asp:DropDownList ID="rows" runat="server">53 <asp:ListItem>1</asp:ListItem>54 <asp:ListItem>2</asp:ListItem>55 <asp:ListItem>3</asp:ListItem>56 <asp:ListItem>4</asp:ListItem>57 </asp:DropDownList>58 <asp:DropDownList ID="cell" runat="server">59 <asp:ListItem>1</asp:ListItem>60 <asp:ListItem>2</asp:ListItem>61 <asp:ListItem>3</asp:ListItem>62 <asp:ListItem>4</asp:ListItem>63 <asp:ListItem>5</asp:ListItem>64 </asp:DropDownList>65 <asp:Button ID="Button1" runat="server" Text="给我建个表格来" OnClick="Button1_Click" /><br />66 <br />67 </div>68 </form>69</body>70</html>71int rn = int.Parse(rows.SelectedValue);//得到需要增加的⾏数int cn = int.Parse(cell.SelectedValue);//得到需要增加的列数先获取要创建表格的⾏数列数：int rn = int.Parse(rows.SelectedValue);int cn = int.Parse(cell.SelectedValue);然后申明表格⾏和表格列：TableRow tr = new TableRow();TableCell tc = new TableCell();给表格内容赋值：tc.Controls.Add(new LiteRalControl("表格⾏内容")); tr.Cells.Add(tc);//把列增加到⾏⾥⾯去添加列到⾏中去：tr.Cells.Add(tc);添加⾏到表格中：Table1.Rows.Add(tr);。

sql动态行转列的两种方法

sql动态⾏转列的两种⽅法第⼀种⽅法：复制代码代码如下:select *from ( select Url,case when Month=01 then '1⽉' when Month=02 then '2⽉' when Month=03 then '3⽉' when Month=04 then '4⽉' when Month=05 then '5⽉' when Month=06 then '6⽉' when Month=07 then '7⽉' when Month=08 then '8⽉' when Month=09 then '9⽉' when Month=10 then ' 10⽉' when Month=11 then '11⽉' when Month=12 then ' 12⽉'end month,Quality from (select Url,DATENAME(M,AuditingTime)Month,SUM(Quality) Quality from tb_order as a left join tb_WebSiteInfo as b on a.WebSiteInfoID=b.ID left join tb_OrderList as c on c.OrderID=a.ID where AuditingTime>'2013-01-01' and b.ID>0 and Auditing=2group by Url,DATENAME(M,AuditingTime) )as h ) as hhpivot ( sum(Quality) for month in([1⽉],[2⽉],[3⽉],[4⽉],[5⽉],[6⽉],[7⽉],[8⽉],[9⽉],[10⽉],[11⽉],[12⽉])) as a第⼆种⽅法：复制代码代码如下:declare @sql varchar(8000)select @sql = isnull(@sql + ',' , '') + '['+CONVERT(varchar(7),AuditingTime,20)+']'from tb_order as a left join tb_WebSiteInfo as b on a.WebSiteInfoID=b.ID left join tb_OrderList as c on c.OrderID=a.ID where AuditingTime>'2013-01-01' and b.ID>0 and Auditing=2group by CONVERT(varchar(7),AuditingTime,20) print @sql declare @sql2 varchar(8000)='' set @sql2=' select *from (select Url, CONVERT(varchar(7),AuditingTime,20) AuditingTime,SUM(Quality) Quality from tb_order as a left jointb_WebSiteInfo as b on a.WebSiteInfoID=b.ID left join tb_OrderList as c on c.OrderID=a.ID where b.ID>0 and Auditing=2group by Url, CONVERT(varchar(7),AuditingTime,20)) as hh pivot (sum(Quality) for AuditingTime in (' + @sql + ')) b'print @sql2exec(@sql2)。

SQLSERVER行列转换（动态）

SQLSERVER⾏列转换（动态）⾏转列测试数据：--测试数据if not object_id(N'Tempdb..#T') is nulldrop table #TGoCreate table #T([Name] nvarchar(22),[Subject] nvarchar(22),[Score] int)Insert #Tselect N'李四',N'语⽂',60 union allselect N'李四',N'数学',70 union allselect N'李四',N'英语',80 union allselect N'张三',N'语⽂',90 union allselect N'张三',N'数学',80 union allselect N'张三',N'英语',70Go--测试数据结束动态写法（加上了总分和平均分）：DECLARE @sql VARCHAR(MAX)SET @sql = 'select Name'SELECT @sql = @sql + ',max(case Subject when ''' + Subject+ ''' then Score else 0 end)[' + Subject + ']'FROM ( SELECT DISTINCTSubjectFROM #T) aSET @sql = @sql+ ',sum(Score) 总分,cast(avg(Score*1.0) as decimal(18,2)) 平均分 from #T group by Name'EXEC(@sql)动态的也可以使⽤pivot：DECLARE @sql VARCHAR(MAX)SELECT @sql=isnull(@sql+',','')+Subject FROM #T GROUP BY SubjectSET @sql='select m.* , n.总分, n.平均分 from(select * from (select * from #T) a pivot (max(Score) for Subject in ('+@sql+')) b) m ,(select Name,sum(Score)总分, cast(avg(Score*1.0) as decimal(18,2))平均分 from #T group by Name) nwhere = 'exec(@sql)列转⾏的测试数据：--测试数据if not object_id(N'Tt') is nulldrop table TtGoCreate table Tt([姓名] nvarchar(22),[语⽂] int,[数学] int,[英语] int)Insert Ttselect N'张三',60,70,80 union allselect N'李四',90,80,70Go--测试数据结束动态写法：DECLARE @sql VARCHAR(8000)SELECT @sql=isnull(@sql+' union all ','')+' select 姓名, [课程]='+quotename(Name,'''')+' , [分数] = '+quotename(Name)+' from T'FROM syscolumnsWHERE Name!='姓名' AND ID=object_id('T')--表名tb，不包含列名为姓名的其他列ORDER BY colidEXEC(@sql+' order by 姓名')同样的动态写法也可以使⽤unpivot：DECLARE @sql VARCHAR(8000)SELECT @sql=isnull(@sql+',','')+quotename(Name)FROM syscolumnsWHERE ID=object_id('T')AND Name NOT IN('姓名')ORDER BY ColidSET @sql='select 姓名,[课程],[分数] from T unpivot ([分数] for [课程] in('+@sql+'))b'exec(@sql)。

MYSQL动态行转列

/*==============================================================*//* 动态行转列 *//* 参数/* tableName :需要行转列的表名 *//* groupName :分组列名 *//* groupNameType :分组列名类型，如：VARCHAR(128)或者bigint(20) *//* hangzhuanglies :需要行转列的列名可指定多列用逗号分隔，如：DiagnosisICDCodeDiagnosisICDDesc *//* notNullHangzhuanglies :行转列中不能为空的列可指定多列用逗号分隔，如：DiagnosisICDCode *//* tableWhere :筛选数据的条件如：and IsFristDiagnosis <> \'Y\'，如果不传值写成 and 1=1 *//* lieRrefix :转为列后的列头前缀，如px，lieRrefix和lieName都传值则以lieRrefix为准注意如果hangzhuanglies指定的为多列则只能用列头前缀的方式 *//* lieName :转为列后以某一列的数据内容作为列头，如DiagnosisICDCode*//* createTableName :查询结果生成物理表名称，如果传值为null或者''表示不生成物理表*//* *//*==============================================================*/DROP PROCEDURE IF EXISTS pro_hang_zhuan_lie;CREATE PROCEDURE `pro_hang_zhuan_lie`(in tableName VARCHAR(128) in groupNameVARCHAR(64) in groupNameType VARCHAR(64) in hangzhuanglies VARCHAR(255) in notNullHangzhuanglies VARCHAR(255) in tableWhere text in lieRrefix VARCHAR(64)in lieNameVARCHAR(64) in createTableName VARCHAR(64))BEGINDECLARE i INT DEFAULT 1 ; #拼接SQL字符串DECLARE split CHAR(2) default '';DECLARE hzlSize INT DEFAULT 0 ; #拼接SQL字符串-- 创建临时表 --drop temporary table if exists TMP_GroupConcatData;set @querySql = CONCAT("create temporary tableTMP_GroupConcatData(groupname_hang_zhuan_lie " groupNameType " not null DataSize int nullGroupConcatData LONGTEXT null GroupConcatLieName LONGTEXT null PRIMARY KEY(`groupname_hang_zhuan_lie`))engine = myisam;");prepare prepSql from @querySql;execute prepSql;deallocate prepare prepSql;-- 替换行转列中的英文逗号为“-”SET @lies = hangzhuanglies;SET @lies = CONCAT(@lies'');SET @g = CONCAT('CONCAT(');WHILE LOCATE(split@lies) DOSELECT SUBSTRING_INDEX(@liessplit1) INTO @lie;SET @lies = SUBSTRING(@liesLOCATE(split@lies)+1);SET @g = CONCAT(@g"replace("@lie"'''-')");SET @g = CONCAT(@g"""\'\'");SET hzlSize = hzlSize + 1 ;END WHILE;SET @g = left(@g length(@g)-5);SET @g = CONCAT(@g')');-- 组拼不能为空的过滤条件SET @notNulllies = notNullHangzhuanglies;SET @notNulllies = CONCAT(@notNulllies'');SET @nu = CONCAT('');WHILE LOCATE(split@notNulllies) DOSELECT SUBSTRING_INDEX(@notNullliessplit1) INTO @notNullLie;SET @notNulllies = SUBSTRING(@notNullliesLOCATE(split@notNulllies)+1);SET @nu = CONCAT(@nu' '@notNullLie" IS NOT NULL AND");END WHILE;SET @nu = left(@nu length(@nu)-3);-- 将分组查询出的数据插入至缓存中set @querySql = "";if(lieRrefix IS NOT NULL AND trim(lieRrefix) <> '') thenset @querySql = CONCAT("INSERT INTO TMP_GroupConcatData SELECT " groupName" groupname_hang_zhuan_lie COUNT(1)*"hzlSize" DataSize GROUP_CONCAT(" @g ") GroupConcatData '' AS GroupConcatLieName FROM " tableName " WHERE " @nu " "tableWhere " GROUP BY " groupName ";");ELSEIF(lieName IS NOT NULL AND trim(lieName) <> '') thenset @querySql = CONCAT("INSERT INTO TMP_GroupConcatData SELECT " groupName" groupname_hang_zhuan_lie COUNT(1)*"hzlSize" DataSize GROUP_CONCAT(" @g ") GroupConcatData GROUP_CONCAT(" lieName ") GroupConcatLieName FROM " tableName " WHERE " @nu " "tableWhere " GROUP BY " groupName ";");END IF;prepare prepSql from @querySql;execute prepSql;deallocate prepare prepSql;-- 查询出最大列值set @querySql = CONCAT("SELECT MAX(DataSize)MAX(GroupConcatLieName) into@maxDataSize @maxLieNames FROM TMP_GroupConcatData;");prepare prepSql from @querySql;execute prepSql;deallocate prepare prepSql;SET @s = CONCAT('SELECT groupname_hang_zhuan_lie');SET @g = @maxDataSize * hzlSize;WHILE i <= @maxDataSize DOif(lieRrefix IS NOT NULL AND trim(lieRrefix) <> '') thenSET @s = CONCAT(@s" CASE WHEN DataSize >= "i" THENsubstring_index(substring_index(GroupConcatData '' "i") '' -1) ELSE NULL END "lieRrefixi'_'ceil(i/hzlSize) " ") ;ELSESELECT substring_index(substring_index(@maxLieNames '' i) '' -1) into @maxLieName;SET @s = CONCAT(@s" CASE WHEN DataSize >= "i" THENsubstring_index(substring_index(GroupConcatData '' "i") '' -1) ELSE NULL END "@maxLieName " ") ; END IF;SET i = i + 1 ;END WHILE ;SET @s = left(@s length(@s)-1);SET @s = CONCAT(@s' FROM TMP_GroupConcatData; ');#用于调试-- SELECT @s;if(createTableName is null or trim(createTableName) = '') thenPREPARE stmt FROM @s ;EXECUTE stmt ;deallocate prepare stmt;ELSESET @d1 = CONCAT("drop table if exists "createTableName);PREPARE stmt1 FROM @d1 ;EXECUTE stmt1 ;deallocate prepare stmt1;SET @s = CONCAT("CREATE TABLE "createTableName' '@s);PREPARE stmt FROM @s ;EXECUTE stmt ;deallocate prepare stmt;SET @s = CONCAT("ALTER TABLE "createTableName" ADD PRIMARY KEY (groupname_hang_zhuan_lie)");PREPARE stmt FROM @s ;EXECUTE stmt ;deallocate prepare stmt;SELECT '行转列物理表生成成功!' info;end if;drop temporary table if exists TMP_GroupConcatData;END;# 例子DROP TABLE IF EXISTS stdscore;create table stdscore(id int auto_incrementprimary keysname varchar(64) nullcname varchar(64) nullscore int null);INSERT INTO stdscore (sname cname score) VALUES ('小王' 'java' 20);INSERT INTO stdscore (sname cname score) VALUES ('小张' 'java' 30);INSERT INTO stdscore (sname cname score) VALUES ('小张' 'mysql' 40);INSERT INTO stdscore (sname cname score) VALUES ('小张' 'linux' 50);CALL pro_hang_zhuan_lie('stdscore''sname''VARCHAR(128)''score''score'' and 1=1 ''''cname''');。

Excel如何用公式，实现行与列的灵活转换？offsetc6a2excel单元格

Excel如何用公式，实现行与列的灵活转换？offsetc6a2excel单元格阅读文本大概需要1-2min.Excel作为小型数据的载体，数据录入区域仅由行和列组成，上手简单、应用面广。

日常数据处理中经常会遇到行与列转换的情况，如多行转一行、一列转多列等，今天我们就系统地介绍下，如何用公式，来实现行与列的灵活转换。

多列转一列：多行转一行：一列转多列：一行转多行：可以发现的是，这些转换全部是用OFFSET函数来实现的，所以先来了解下OFFSET函数的功能。

OFFSET函数功能为：以指定的引用为参照系，通过给定偏移量返回新的引用。

表达式：OFFSET(引用区域, 行偏移量, 列偏移量, [返回行高], [返回列宽])，一共五个参数，后面两个可以省略，举个例子加深对函数的理解：例1OFFSET(A2,4,1)意思是参照A2单元格，向下偏移4行、向右偏移1列：A2单元格位于第2行、向下偏移4行就是第6行；A2单元格位于第1列（A列）、向右偏移1列就是第2列（B 列）；所以OFFSET(A2,4,1)最后返回的单元格是B6单元格的内容，即51。

例2OFFSET(C6,-3,-2)意思是参考C6单元格，向上偏移3行（参数2负号表示向上）、向左偏移2列（参数3负号表示向左）：C6单元格位于第6行、向上偏移3行就是第3行；C6单元格位于第3列（C列）、向左偏移2列就是第1列(A列)；所以OFFSET(C6,-3,-2)最后返回A3单元格的内容，即56。

总结：以第一个参数为参照物，向下（正数）或向上（负数）偏移X行（参数2的绝对值）；向右（正数）或向左（负数）偏移Y列（参数3的绝对值），所得的单元格内容，即为公式返回值。

弄清楚函数的功能，再来看今天的行列转换就要轻松很多，我们以多列转一列为例，来看下函数的具体用法。

下图中，需要将A1:C5共15个单元的内容竖向排列（转1列），首先排列第一行A1-C1单元格，接着排列A2-C2单元格…以此类推。

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表格列转行的方法

excel行列对调的方法

excel行列互换的方法

行列互换其值不变证明

oracle动态行转不定列

Excel表格里行和列数据交换位置的技巧(转置)

Oracle行转列（不固定行数的行转列，动态）（转）

oracle动态行变列

动态改变表格的行数列数（添加表格）

sql动态行转列的两种方法

SQLSERVER行列转换（动态）

MYSQL动态行转列

Excel如何用公式，实现行与列的灵活转换？offsetc6a2excel单元格

gaussdb 行转列函数