经常折腾数据库，常常遇到数据库行列转换的问题，下面就用一个小例子来演示下如何进行行列转换。
1.创建一张表
CREATE TABLE [android_source](
[CREATETIME] [datetime] NULL,
[SOURCE] [nvarchar](255) NULL,
[COUNT] [float] NULL
)
2.插入测试数据
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-13 00:00:00.000','1xingdong',3);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-13 00:00:00.000','eoemarket',1);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-13 00:00:00.000','lenovomm',2);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-13 00:00:00.000','mqidian',2);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-13 00:00:00.000','mumayi',5);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-13 00:00:00.000','nduoa',1);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-13 00:00:00.000','paojiao',4);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-13 00:00:00.000','qidian',5);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-14 00:00:00.000','1xingdong',20);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-14 00:00:00.000','eoemarket',7);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-14 00:00:00.000','lenovomm',21);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-14 00:00:00.000','mqidian',43);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-14 00:00:00.000','mumayi',17);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-14 00:00:00.000','nduoa',14);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-14 00:00:00.000','paojiao',8);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-14 00:00:00.000','qidian',72);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-15 00:00:00.000','1xingdong',30);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-15 00:00:00.000','eoemarket',22);
insert android_source (CREATETIME,SOURCE,COUNT) values ( '2013-07-15 00:00:00.000','lenovomm',32);
3.列转行
SELECT
CONVERT(varchar(10), [CREATETIME],120) as CreateTime,
MAX(CASE [SOURCE] WHEN 'qidian' THEN [COUNT] ELSE 0 END) AS 'qidian',
MAX(CASE [SOURCE] WHEN 'paojiao' THEN [COUNT] ELSE 0 END) AS 'paojiao',
MAX(CASE [SOURCE] WHEN 'nduoa' THEN [COUNT] ELSE 0 END) AS 'nduoa',
MAX(CASE [SOURCE] WHEN 'mumayi' THEN [COUNT] ELSE 0 END) AS 'mumayi',
MAX(CASE [SOURCE] WHEN 'mqidian' THEN [COUNT] ELSE 0 END) AS 'mqidian',
MAX(CASE [SOURCE] WHEN 'lenovomm' THEN [COUNT] ELSE 0 END) AS 'lenovomm',
MAX(CASE [SOURCE] WHEN 'eoemarket' THEN [COUNT] ELSE 0 END) AS 'eoemarket',
MAX(CASE [SOURCE] WHEN 'dbank' THEN [COUNT] ELSE 0 END) AS 'dbank',
MAX(CASE [SOURCE] WHEN '1xingdong' THEN [COUNT] ELSE 0 END) AS '1xingdong'
FROM [MyDataBase].[dbo].android_source
GROUP BY CONVERT(varchar(10), [CREATETIME],120)
结果：
