excel数据excel 透视表 行转列列的问题

来源：蜘蛛抓取(WebSpider) 时间：2016-08-09 09:27 标签： excel批量行转列
您的位置： >
SQL中PIVOT 行列转换
学习标签：
本文导读：T-SQL语句中，Pivot运算符用于在列和行之间对数据进行旋转或透视转换，PIVOT命令可以实现数据表的列转行，同时执行聚合运算，UNPIVOT则与其相反，实现数据的行转列。
PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT与PIVOT执行相反的操作，将表值表达式的列转换为列值。
通俗简单的说：PIVOT就是行转列，UNPIVOT就是列传行
一、PIVOT实例
建立一个销售情况表，其中，year字段表示年份，quarter字段表示季度，amount字段表示销售额。quarter字段分别用Q1, Q2, Q3, Q4表示一、二、三、四季度。
SQL 代码 &&复制
CREATE TABLE SalesByQuarter
quarter CHAR(2),
amount MONEY
2. 填入表数据
使用如下程序填入表数据。
SQL 代码 &&复制
SET NOCOUNT ON
DECLARE @index INT
DECLARE @q INT
SET @index = 0
DECLARE @year INT
while (@index & 30)
SET @year = 2005 + (@index % 4)
SET @q = (CAST((RAND() * 500) AS INT) % 4) + 1
INSERT INTO SalesByQuarter VALUES (@year, 'Q' + CAST(@q AS CHAR(1)), RAND() * 10000.00)
SET @index = @index + 1
3、如果我们要比较每年中各季度的销售状况，要怎么办呢？有以下两种方法：
（1）、使用传统Select的CASE语句查询
在SQL Server以前的版本里，将行级数据转换为列级数据就要用到一系列CASE语句和聚合查询。虽然这种方式让开发人员具有了对所返回数据进行高度控制的能力，但是编写出这些查询是一件很麻烦的事情。
SQL 代码 &&复制
SELECT year as 年份
, sum (case when quarter = 'Q1' then amount else 0 end) 一季度
, sum (case when quarter = 'Q2' then amount else 0 end) 二季度
, sum (case when quarter = 'Q3' then amount else 0 end) 三季度
, sum (case when quarter = 'Q4' then amount else 0 end) 四季度
FROM SalesByQuarter GROUP BY year ORDER BY year DESC
得到的结果如下：
（2）、使用PIVOT
由于SQL Server 2005有了新的PIVOT运算符，就不再需要CASE语句和GROUP BY语句了。（每个PIVOT查询都涉及某种类型的聚合，因此你可以忽略GROUP BY语句。）PIVOT运算符让我们能够利用CASE语句查询实现相同的功能，但是你可以用更少的代码就实现，而且看起来更漂亮。
SQL 代码 &&复制
SELECT year as 年份, Q1 as 一季度, Q2 as 二季度, Q3 as 三季度, Q4 as 四季度 FROM SalesByQuarter PIVOT (SUM (amount) FOR quarter IN (Q1, Q2, Q3, Q4) ) AS P ORDER BY YEAR DESC
得到的结果如下：
二、通过下面一个实例详细介绍PIVOT的过程
SQL 代码 &&复制
SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]--这里是PIVOT第三步（选择行转列后的结果集的列）这里可以用&*&表示选择所有列，也可以只选择某些列(也就是某些天)
FROM WEEK_INCOME --这里是PIVOT第二步骤(准备原始的查询结果，因为PIVOT是对一个原始的查询结果集进行转换操作，所以先查询一个结果集出来)这里可以是一个select子查询，但为子查询时候要指定别名，否则语法错误
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--别名一定要写
三.UNPIVOT
很明显，UN这个前缀表明了，它做的操作是跟PIVOT相反的，即列转行。UNPIVOT操作涉及到以下三个逻辑处理阶段。
1，生成副本
2，提取元素
3，删除带有NULL的行
UNPIVOT实例
SQL 代码 &&复制
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
--Unpivot the table.
SELECT VendorID, Employee, Orders
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
上面UNPIVOT实例的分析
UNPIVOT的输入是左表表达式P，第一步，先为P中的行生成多个副本，在UNPIVOT中出现的每一列，都会生成一个副本。因为这里的IN子句有5个列名称，所以要为每个来源行生成5个副本。结果得到的虚拟表中将新增一个列，用来以字符串格式保存来源列的名称（for和IN之间的，上面例子是 Employee ）。第二步，根据新增的那一列中的值从来源列中提取出与列名对应的行。第三步，删除掉结果列值为null的行，完成这个查询。
您可能感兴趣
一月好评排行榜查看: 7484|回复: 10
大量单列数据按需特定条件，行转列，与，列转行，该怎么做？
阅读权限10
在线时间 小时
先说声抱歉啦，我个人的基础基本很差，较少接触办公软件，可是遇到了这个问题：
数据为单列，已有接近六万行，以后数据还要增加及整理，
数据中有英文和数字，每行前面必须有一个空格，上传的文件并不是失误。
想每九行转为一列，以此类推。
另还有九列以下的文件想转换成单列，也就是能互相转换。（数据中的列数，参差不齐，我觉得可能有点难度）
所以想请教各位高手，谢谢。
具体请查看附件，里面包含一个表格一个文本。
00:11 上传
点击文件名下载附件
10.45 KB, 下载次数: 60
阅读权限90
在线时间 小时
选择性粘贴，转置！
阅读权限10
在线时间 小时
& & & & & & & &
回复 2楼 亡者天下 的帖子
接近六万行，你的主意我想过，行不通，以后每维护一次，还不累死，效益太低才想各位帮忙的。
阅读权限90
在线时间 小时
你前面已经提问过，已有人回复了公式，新回复又给了宏代码，你看看去。
阅读权限50
在线时间 小时
各种奇怪的要求，真是开眼界了。
阅读权限50
在线时间 小时
楼主自己这样计算一下：几万行记录除以9等于多少，起码1000以上。而excel中只能排255列，试问怎么把1000组数据分别排到255列？这样的要求有点天方夜谭吧
阅读权限10
在线时间 小时
回复 6楼 抹香鲸2009 的帖子
我用永中的office，它的表格可以大概有65536行，请看清楚，一行，只有九列，谢谢。
阅读权限20
在线时间 小时
用offset函数可做到
阅读权限90
在线时间 小时
楼主还是再回原来的提问贴去看看吧，与其在这里争论永中与EXCEL，不如去弄清楚“为什么没反应，怎样才能使宏运行？”
那边已经给你做了“面条”，并告诉你“这比做牛排快，你现在就可以享用了，你吃不到，那是因为你没有用‘筷子’。”
[ 本帖最后由 cbtaja 于
07:51 编辑 ]
阅读权限50
在线时间 小时
原帖由 bocs 于
22:29 发表
我用永中的office，它的表格可以大概有65536行，请看清楚，一行，只有九列，谢谢。
1楼楼主原话：“想每九行转为一列，以此类推。”拜托自己看清楚再提问，ok
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师

我要回帖

更多关于 excel批量行转列 的文章

 

随机推荐