推荐订阅源

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 代码,进货单位