求sql 语句 行转列
大家好！
数据库中有一张A表。现在要用SQL语句转换成B表格式显示出来！详情见下图！
谢谢！
[解决办法]
给你参考个实例，在自己弄下
IF exists (select * from sysobjects where name = 'rece')
drop table rece
go
create table rece
(
billtype VARCHAR(20),
name varchar(20),
money int
)
go
insert into rece values ('应收','运费',100)
insert into rece values ('应收','杂费',50)
insert into rece values ('应收','报关费',80)
insert into rece values ('应付','运费',80)
insert into rece values ('应付','商检费',60)
go
select * from rece
go
select 费用名称=A.NAME,应收金额=isnull(B.summoney,0),应付金额= isnull(C.summoney,0)
from
--查找所有费用名称(去掉重复的)
(select distinct name from rece )A
left join
--查找应收总金额 根据费用名字分组
(select sum(isnull(money,0)) as summoney,name from rece where billtype ='应收' group by name)B on A.name = B.name
left join
--查找应付总金额 根据费用名字分组
(select sum(isnull(money,0)) as summoney,name from rece where billtype ='应付' group by name)C on A.name = C.name
[解决办法]
DECLARE @t TABLE(depname VARCHAR(10),sorname VARCHAR(10),point INT,ct INT);
INSERT INTO @t VALUES
('生产处','橡胶部',200,1)
,('生产处','制一管材',1000,1)
,('生产处','制造二部',100,1)
,('研发处','模具部',100,1)
,('资料处','制一前加工',200,1)
----SELECT depname+'/'+sorname AS n,point,ct FROM @t
SELECT '奖金' AS [xx],[生产处/橡胶部] AS [生产处/橡胶部],[生产处/制一管材] AS [生产处/制一管材],[生产处/制造二部] AS [生产处/制造二部],[研发处/模具部] AS [研发处/模具部],[资料处/制一前加工] AS [资料处/制一前加工] FROM (
SELECT depname+'/'+sorname AS n,point FROM @t
) a PIVOT (SUM(point) FOR n IN ([生产处/橡胶部],[生产处/制一管材],[生产处/制造二部],[研发处/模具部],[资料处/制一前加工])) pt
UNION ALL
SELECT '次数' AS [xx],[生产处/橡胶部] AS [生产处/橡胶部],[生产处/制一管材] AS [生产处/制一管材],[生产处/制造二部] AS [生产处/制造二部],[研发处/模具部] AS [研发处/模具部],[资料处/制一前加工] AS [资料处/制一前加工] FROM (
SELECT depname+'/'+sorname AS n,ct FROM @t
) a PIVOT (SUM(ct) FOR n IN ([生产处/橡胶部],[生产处/制一管材],[生产处/制造二部],[研发处/模具部],[资料处/制一前加工])) pt
/*
xx 生产处/橡胶部 生产处/制一管材 生产处/制造二部 研发处/模具部 资料处/制一前加工
---- ----------- ----------- ----------- ----------- -----------
奖金 200 1000 100 100 200
次数 1 1 1 1 1
*/
