DB: 将sql查询结果的行转换为列

有的时候,需要将sql查询结果建立成视图以供使用(或者每次使用该sql作为其它查询的子语句)。但是,在某些情况下,比如出于统计的需要,需将这种查询结果的某一字段的各行转换为列。

例子一:

比如以下查询:

 

select  t.l_year, t.l_month, t.l_xswcqk, count(*) as tnum
 from
(select * from sc_ljyyxbclsg_xs x,sc_ljyyxbclsg g where x.l_xsyyxbclsg=g.l_id)t
group by t.l_xswcqk, t.l_year, t.l_month order by t.l_month;

查询结果如下图:

但必须根据L_XSWCQK的值建立对应的列才能够完成后继工作。

其中,sc_ljyyxbclsg为主表,sc_ljyyxbclsg_xs为从表。sc_liyyxbclsg_xs.lxsyyxbclsg引用主表的l_id做外键。

主表sc_ljyyxbclsg结构:

从表sc_ljyyxbclsg_xs结构:

主从表笛卡尔积:

使用如下的查询将行转换为列:

select  t.l_year, t.l_month, t.l_xswcqk,
count(case when t.l_xswcqk='计划' then 1 else null end) as plannum,
count(case when t.l_xswcqk='完成' then 1 else null end) as compnum,
count(case when t.l_xswcqk='未完成' then 1 else null end) as uncompnum,
count(case when t.l_xswcqk='取消' then 1 else null end) as cancnum,
count(case when t.l_xswcqk is null then 1 else null end) as nullnum,
count(*) as tnum
 from
(select * from sc_ljyyxbclsg_xs x,sc_ljyyxbclsg g where x.l_xsyyxbclsg=g.l_id)t
group by t.l_xswcqk, t.l_year, t.l_month order by t.l_month;

该语句,根据从表l_xswcqk的不同值建立不同名称的列。

查询结果如下:

为消除l_xswcqk列(该列已经多余),将以上sql语句修改并建立视图:

create or replace view sc_v_yyxbclsg_tj as
select  t.l_year, t.l_month,
count(case when t.l_xswcqk='计划' then 1 else null end) as plannum,
count(case when t.l_xswcqk='完成' then 1 else null end) as compnum,
count(case when t.l_xswcqk='未完成' then 1 else null end) as uncompnum,
count(case when t.l_xswcqk='取消' then 1 else null end) as cancnum,
count(case when t.l_xswcqk is null then 1 else null end) as nullnum,
count(*) as tnum
 from
(select * from sc_ljyyxbclsg_xs x,sc_ljyyxbclsg g where x.l_xsyyxbclsg=g.l_id)t
group by t.l_year, t.l_month order by t.l_month;

视图数据:

 

例子二:

有表:

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

下面来了一个统计数据的需求,要求按日期、支付方式来统计充值金额信息。这也是一个典型的行转列的例子。我们可以通过下面的脚本来达到目的

SELECT CONVERT(VARCHAR(10), CreateTime, 120AS CreateTime,
CASE PayType WHEN 支付宝     THEN SUM(MoneyELSE 0 END AS 支付宝,
CASE PayType WHEN 手机短信    THEN SUM(MoneyELSE 0 END AS 手机短信,
CASE PayType WHEN 工商银行卡  THEN SUM(MoneyELSE 0 END AS 工商银行卡,
CASE PayType WHEN 建设银行卡  THEN SUM(MoneyELSE 0 END AS 建设银行卡
FROM Inpours
GROUP BY CreateTime, PayType

//参考:http://www.cnblogs.com/kerrycode/archive/2010/07/28/1786547.html

This entry was posted in Computer, Database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s