SQL行转列与列转行问题

正文

行转列，列转行是我们在开发过程中经常碰到的问题。

1、行转列一般通过CASE WHEN 语句来实现

2、也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。

用传统的方法，比较好理解。层次清晰行转列，而且比较习惯。

但是PIVOT 、UNPIVOT提供的语法比一系列复杂的SELECT…CASE 语句中所指定的语法更简单、更具可读性。下面我们通过几个简单的例子来介绍一下列转行、行转列问题。

一、行转列

我们首先先通过一个老生常谈的例子，学生成绩表(下面简化了些)来形象了解下行转列

CREATE  TABLE [StudentScores](   [UserName]         NVARCHAR(20),        --学生姓名    [Subject]          NVARCHAR(30),        --科目    [Score]            FLOAT,               --成绩)

插入数据

INSERT INTO [StudentScores] SELECT 'Nick', '语文', 80INSERT INTO [StudentScores] SELECT 'Nick', '数学', 90INSERT INTO [StudentScores] SELECT 'Nick', '英语', 70INSERT INTO [StudentScores] SELECT 'Nick', '生物', 85INSERT INTO [StudentScores] SELECT 'Kent', '语文', 80INSERT INTO [StudentScores] SELECT 'Kent', '数学', 90INSERT INTO [StudentScores] SELECT 'Kent', '英语', 70INSERT INTO [StudentScores] SELECT 'Kent', '生物', 85

如果我想知道每位学生的每科成绩，而且每个学生的全部成绩排成一行，这样方便我查看、统计行转列，导出数据

1、case when 方式

SELECT UserName,       MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文',      MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学',      MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语',      MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物'FROM dbo.[StudentScores]GROUP BY UserName

查询结果如图所示，这样我们就能很清楚的了解每位学生所有的成绩了

行转列_css图片排版2行2列_mysql 列与列合并行与行合并

2、PIVOT方式

SELECT * FROM [StudentScores] /*数据源*/AS PPIVOT (    SUM(Score/*行转列后 列的值*/) FOR     p.Subject/*需要行转列的列*/ IN ([语文],[数学],[英语],[生物]/*列的值*/)) AS T

另举一例做解释：

SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]--这里是PIVOT第三步（选择行转列后的结果集的列）这里可以用“*”表示选择所有列，也可以只选择某些列(也就是某些天),通过TBL来定义列的别名
FROM WEEK_INCOME --这里是PIVOT第二步骤(准备原始的查询结果，因为PIVOT是对一个原始的查询结果集进行转换操作，所以先查询一个结果集出来)这里可以是一个select子查询， --但为子查询时候要指定别名，否则语法错误
PIVOT(    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--别名一定要写

二、列转行

1、测试数据准备

CREATE TABLE ProgrectDetail(    ProgrectName         NVARCHAR(20), --工程名称    OverseaSupply        INT,          --海外供应商供给数量    NativeSupply         INT,          --国内供应商供给数量    SouthSupply          INT,          --南方供应商供给数量    NorthSupply          INT           --北方供应商供给数量)
INSERT INTO ProgrectDetailSELECT 'A', 100, 200, 50, 50UNION ALLSELECT 'B', 200, 300, 150, 150UNION ALLSELECT 'C', 159, 400, 20, 320UNION ALL

sql语句：

SELECT P.ProgrectName,P.Supplier,P.SupplyNumFROM (    SELECT ProgrectName, OverseaSupply, NativeSupply,           SouthSupply, NorthSupply     FROM ProgrectDetail)TUNPIVOT (    SupplyNum FOR Supplier IN    (OverseaSupply, NativeSupply, SouthSupply, NorthSupply )) P