行转列，列转行

行转列，列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。用传统的方法，比较好理解。层次清晰，而且比较习惯。 但是PIVOT 、UNPIVOT提供的语法比一系列复杂的SELECT…CASE 语句中所指定的语法更简单、更具可读性。下面我们通过几个简单的例子来介绍一下列转行、行转列问题。

我们首先先通过一个老生常谈的例子，学生成绩表(下面简化了些)来形象了解下行转列
CREATE TABLE [StudentScores]
(
[UserName] NVARCHAR(20), –学生姓名
[Subject] NVARCHAR(30), –科目
[Score] FLOAT, –成绩
)

INSERT INTO [StudentScores] SELECT ‘Nick’, ‘语文’, 80

INSERT INTO [StudentScores] SELECT ‘Nick’, ‘数学’, 90

INSERT INTO [StudentScores] SELECT ‘Nick’, ‘英语’, 70

INSERT INTO [StudentScores] SELECT ‘Nick’, ‘生物’, 85

INSERT INTO [StudentScores] SELECT ‘Kent’, ‘语文’, 80

INSERT INTO [StudentScores] SELECT ‘Kent’, ‘数学’, 90

INSERT INTO [StudentScores] SELECT ‘Kent’, ‘英语’, 70

INSERT INTO [StudentScores] SELECT ‘Kent’, ‘生物’, 85
如果我想知道每位学生的每科成绩，而且每个学生的全部成绩排成一行，这样方便我查看、统计，导出数据
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

接下来我们来看看第二个小列子。有一个游戏玩家充值表（仅仅为了说明，举的一个小例子），
CREATE TABLE [Inpours]
(
[ID] INT IDENTITY(1,1),
[UserName] NVARCHAR(20), –游戏玩家
[CreateTime] DATETIME, –充值时间
[PayType] NVARCHAR(20), –充值类型
[Money] DECIMAL, –充值金额
[IsSuccess] BIT, –是否成功 1表示成功， 0表示失败
CONSTRAINT [PK_Inpours_ID] PRIMARY KEY(ID)
)

INSERT INTO Inpours SELECT ‘张三’, ‘2010-05-01’, ‘支付宝’, 50, 1

INSERT INTO Inpours SELECT ‘张三’, ‘2010-06-14’, ‘支付宝’, 50, 1

INSERT INTO Inpours SELECT ‘张三’, ‘2010-06-14’, ‘手机短信’, 100, 1

INSERT INTO Inpours SELECT ‘李四’, ‘2010-06-14’, ‘手机短信’, 100, 1

INSERT INTO Inpours SELECT ‘李四’, ‘2010-07-14’, ‘支付宝’, 100, 1

INSERT INTO Inpours SELECT ‘王五’, ‘2010-07-14’, ‘工商银行卡’, 100, 1

INSERT INTO Inpours SELECT ‘赵六’, ‘2010-07-14’, ‘建设银行卡’, 100, 1
下面来了一个统计数据的需求，要求按日期、支付方式来统计充值金额信息。这也是一个典型的行转列的例子。我们可以通过下面的脚本来达到目的
代码

Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/–>SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,
CASE PayType WHEN ‘支付宝’ THEN SUM(Money) ELSE 0 END AS ‘支付宝’,

   CASE PayType WHEN '手机短信'    THEN SUM(Money) ELSE 0 END AS '手机短信',

   CASE PayType WHEN '工商银行卡'  THEN SUM(Money) ELSE 0 END AS '工商银行卡',

   CASE PayType WHEN '建设银行卡'  THEN SUM(Money) ELSE 0 END AS '建设银行卡'

FROM Inpours

GROUP BY CreateTime, PayType
如图所示，我们这样只是得到了这样的输出结果，还需进一步处理，才能得到想要的结果
SELECT
CreateTime,
ISNULL(SUM([支付宝]) , 0) AS [支付宝] ,
ISNULL(SUM([手机短信]) , 0) AS [手机短信] ,
ISNULL(SUM([工商银行卡]), 0) AS [工商银行卡] ,
ISNULL(SUM([建设银行卡]), 0) AS [建设银行卡]
FROM
(
SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,
CASE PayType WHEN ‘支付宝’ THEN SUM(Money) ELSE 0 END AS ‘支付宝’ ,
CASE PayType WHEN ‘手机短信’ THEN SUM(Money) ELSE 0 END AS ‘手机短信’,
CASE PayType WHEN ‘工商银行卡’ THEN SUM(Money) ELSE 0 END AS ‘工商银行卡’,
CASE PayType WHEN ‘建设银行卡’ THEN SUM(Money) ELSE 0 END AS ‘建设银行卡’
FROM Inpours
GROUP BY CreateTime, PayType
) T
GROUP BY CreateTime

全文到http://blog.jobbole.com/85800/