求sql 语句 行转列

[maoyunaa]

大家好！
数据库中有一张A表。现在要用SQL语句转换成B表格式显示出来！详情见下图！

谢谢！

[曲军昌]

很遗憾的告诉你，在数据库中搞不成这样的格式，红色标记的必须去掉。 如果你想要红色去掉的格式的话，我给你写。。。。

[maoyunaa]

自己已经搞定了，谢谢大家：代码如下： DECLARE @MS VARCHAR(MAX) Declare @PivotSQL NVARCHAR(MAX) SELECT @MS = COALESCE(@MS + ',[' + cast(MS as nvarchar) + ']', '[' + cast(MS as nvarchar) + ']') FROM (SELECT DISTINCT LTRIM(RTRIM(部门))+'-'+LTRIM(RTRIM(课别)) MS FROM PALAF WHERE 1=1 ) AS A ORDER BY MS SET @PivotSQL = N' SELECT B.RB AS 部门,'+@MS+',HJCS AS ''合计'' FROM (SELECT RB, LTRIM(RTRIM(部门))+''-''+LTRIM(RTRIM(课别)) AS MS,COUNT(*) AS CS FROM PALAF WHERE 1=1 GROUP BY 部门,课别 ) AS AA pivot (sum([CS]) for [MS] in('+@MS+') ) B INNER JOIN (SELECT ''次数'' AS RB,COUNT(*) AS HJCS FROM PALAF WHERE 1=1 ) AS CC ON B.RB=CC.RB UNION ALL SELECT B.RB,'+@MS+',HJJE FROM (SELECT ''金额'' AS RB, LTRIM(RTRIM(部门))+''-''+LTRIM(RTRIM(课别)) AS MS,SUM(金额) AS JE FROM PALAF WHERE 1=1 GROUP BY 部门,课别 ) AS AA pivot (sum([JE]) for [MS] in('+@MS+') ) B INNER JOIN (SELECT ''金额'' AS RB,SUM(金额) AS HJJE FROM PALAF WHERE 1=1 ) AS CC ON B.RB=CC.RB ' execute sp_executesql @PivotSQL

[zhengnan2012]

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

*/

妹的，好久没写了，都快写不出来了。

[-Tracy-McGrady-]

谢谢，给分吧

[夏小笨]

图看不清楚。 不过你要行转列的话就用case when 语法吧 。

[maoyunaa]

夏小笨 您好：见下图

[MrYangkang]

给你参考个实例，在自己弄下 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

[曲军昌]

引用 楼主 maoyunaa 的回复:

大家好！ 数据库中有一张A表。现在要用SQL语句转换成B表格式显示出来！详情见下图！ 谢谢！

能不能把你的表数据贴出来。。。。