java中纵表转横表_纵表变横表经典中的经典

drop table t1

CREATE TABLE [t1] (

[id] [int] IDENTITY (1, 1) NOT NULL ,

[xm] [varchar] (50) default '',

[km] [varchar] (50) default '',

[fs] [float] NULL default 0

) ON [PRIMARY]

insert into t1(xm,km,fs)

select '张三','数学',10 union all

select '张三','语文',20 union all

select '张三','英语',30 union all

select '李四','数学',40 union all

select '李四','语文',50 union all

select '李四','英语',60 union all

select '王五','英语',70

SELECT

xm,

MAX(数学) AS 数学,

MAX(语文) AS 语文,

MAX(英语) AS 英语

FROM

(

SELECT

xm,

CASE km WHEN '数学' THEN fs END AS 数学,

CASE km WHEN '语文' THEN fs END AS 语文,

CASE km WHEN '英语' THEN fs END AS 英语

FROM t1

) AS a

GROUP BY xm order by xm

select * from t1

select xm,

"数学"=(select fs from t1 where km='数学' and xm=t.xm),

"语文"=(select fs from t1 where km='语文' and xm=t.xm),

"英语"=(select fs from t1 where km='英语' and xm=t.xm)

from t1 t group by xm

select 代码,进货单位,

"西药"=(select 让利金额 from abc where 药类 ='西药' and 代码=t1.代码),

"中成药"=(select 让利金额 from abc where 药类 ='中成药' and 代码=t1.代码),

"医材 "=(select 让利金额 from abc where 药类 ='医材' and 代码=t1.代码),

"中草药"=(select 让利金额 from abc where 药类 ='中草药' and 代码=t1.代码),

"合计"=(select sum(药类) from abc where  代码=t1.代码 group by 代码)

from  abc  t1 group by 代码,进货单位