(select bbd.Consignor[委托方],t.[count][票数],
sum(case when CostType = '0' then ShouldMoney else 0 end)[应收],--[ShouldMoney_R],
sum(case when CostType = '1' then ShouldMoney else 0 end)[应付],--[ShouldMoney_P],
(sum(case when CostType = '0' then ShouldMoney else 0 end)
-
sum(case when CostType = '1' then ShouldMoney else 0 end))[利润],--[Profit],
ccd.Flag_Customs[进出口]
FROM customs_CustomsDeclaration as ccd
INNER JOIN cost_FeeInfo AS cfi ON cfi.BusinessNum = ccd.BusinessNum
INNER JOIN bus_BusinessDetail AS bbd ON bbd.BusinessNum = ccd.BusinessNum
INNER JOIN (select count(1)[count],Consignor from bus_BusinessDetail group by Consignor) t
ON t.Consignor = bbd.Consignor
group by bbd.Consignor,[count],Flag_Customs)
(
select bbd.Consignor[委托方],t.[count][票数],
sum(case when CostType = '0' then ShouldMoney else 0 end)[应收],--[ShouldMoney_R],
sum(case when CostType = '1' then ShouldMoney else 0 end)[应付],--[ShouldMoney_P],
(sum(case when CostType = '0' then ShouldMoney else 0 end)
-
sum(case when CostType = '1' then ShouldMoney else 0 end))[利润],--[Profit],
ccd.Flag_Customs[进出口]
FROM customs_CustomsDeclaration as ccd
INNER JOIN cost_FeeInfo AS cfi ON cfi.BusinessNum = ccd.BusinessNum
INNER JOIN bus_BusinessDetail AS bbd ON bbd.BusinessNum = ccd.BusinessNum
INNER JOIN (select count(1)[count],Consignor from bus_BusinessDetail group by Consignor) t
ON t.Consignor = bbd.Consignor
group by bbd.Consignor,[count],Flag_Customs
)
pivot(max([票数]) for course in([委托方],[票数],[进出口]))
;with temp1 ([委托方],[票数],[进出口])as
(
select bbd.Consignor[委托方],t.[count][票数],
sum(case when CostType = '0' then ShouldMoney else 0 end)[应收],--[ShouldMoney_R],
sum(case when CostType = '1' then ShouldMoney else 0 end)[应付],--[ShouldMoney_P],
(sum(case when CostType = '0' then ShouldMoney else 0 end)
-
sum(case when CostType = '1' then ShouldMoney else 0 end))[利润],--[Profit],
ccd.Flag_Customs[进出口]
FROM customs_CustomsDeclaration as ccd
INNER JOIN cost_FeeInfo AS cfi ON cfi.BusinessNum = ccd.BusinessNum
INNER JOIN bus_BusinessDetail AS bbd ON bbd.BusinessNum = ccd.BusinessNum
INNER JOIN (select count(1)[count],Consignor from bus_BusinessDetail group by Consignor) t
ON t.Consignor = bbd.Consignor
group by bbd.Consignor,[count],Flag_Customs
)select * from temp1 pivot(max([票数]) for course in([委托方],[票数],[进出口]))
你是要贴出所有的列吧,之前给你的就只有三列,如果是列出所有的列的话,就更新下:
;with temp1 ([委托方],[票数],[应收],[应付],[利润],[进出口])as
(
select bbd.Consignor[委托方],t.[count][票数],
sum(case when CostType = '0' then ShouldMoney else 0 end)[应收],--[ShouldMoney_R],
sum(case when CostType = '1' then ShouldMoney else 0 end)[应付],--[ShouldMoney_P],
(sum(case when CostType = '0' then ShouldMoney else 0 end)
-
sum(case when CostType = '1' then ShouldMoney else 0 end))[利润],--[Profit],
ccd.Flag_Customs[进出口]
FROM customs_CustomsDeclaration as ccd
INNER JOIN cost_FeeInfo AS cfi ON cfi.BusinessNum = ccd.BusinessNum
INNER JOIN bus_BusinessDetail AS bbd ON bbd.BusinessNum = ccd.BusinessNum
INNER JOIN (select count(1)[count],Consignor from bus_BusinessDetail group by Consignor) t
ON t.Consignor = bbd.Consignor
group by bbd.Consignor,[count],Flag_Customs
)select * from temp1 pivot(max([票数]) for course in([委托方],[票数],[应收],[应付],[利润],[进出口]))
把其他几列加到临时视图里头,我刚开始没注意你那里头还有其他几列。用6楼的试试
create table #temp
(
Consignor varchar(100),
票数 int,
应收 decimal(18,4),
应付 decimal(18,4),
利润 decimal(18,4),
进出口 varchar(1)
)
--Consignor 票数 应收 应付 利润 进出口
select '青岛川汇国际物流有限公司', 28 ,6851.0000, 4153.6400, 2697.3600, '0'
union all
select '青岛正乐食品有限公司' ,30, 2438.0000, 536.3600, 1901.6400, '0'
union all
select '青岛正乐食品有限公司', 30 ,720.0000 ,139.9200 ,580.0800 ,'1'
select '万华化学集团股份有限公司', 8, 840.0000, 186.5600, 653.4400, '0'
union all
select '青岛中远国际货运有限公司' ,253 ,98838.0000 ,26353.2800 ,72484.7200 ,'0'
union all
select '青岛中远国际货运有限公司' ,253, 6020.0000, 146.6400, 5873.3600 ,'1'
union all
select '青岛中远国际货运有限公司济南分公司', 18 ,2398.3200, 373.1200 ,2025.2000 ,'0'
--(
--Consignor varchar(100),
--票数 int,
--应收 decimal(18,4),
--应付 decimal(18,4),
--利润 decimal(18,4),
--进出口 varchar(1)
--)
--INSERT INTO #temp
----Consignor 票数 应收 应付 利润 进出口
--select '青岛川汇国际物流有限公司', 28 ,6851.0000, 4153.6400, 2697.3600, '0'
--union all
--select '青岛正乐食品有限公司' ,30, 2438.0000, 536.3600, 1901.6400, '0'
--union all
--select '青岛正乐食品有限公司', 30 ,720.0000 ,139.9200 ,580.0800 ,'1'
--UNION ALL
--select '万华化学集团股份有限公司', 8, 840.0000, 186.5600, 653.4400, '0'
--union all
--select '青岛中远国际货运有限公司' ,253 ,98838.0000 ,26353.2800 ,72484.7200 ,'0'
--union all
--select '青岛中远国际货运有限公司' ,253, 6020.0000, 146.6400, 5873.3600 ,'1'
--union all
--select '青岛中远国际货运有限公司济南分公司', 18 ,2398.3200, 373.1200 ,2025.2000 ,'0'--SELECT consignor,票数,利润,进出口 FROM #tempdeclare @s nvarchar(MAX)
DECLARE @s1 NVARCHAR(max)
set @s=''
SET @s1=''
Select @s=@s+','+quotename(consignor)+'=sum(case when [consignor]='+quotename(consignor,'''')+' and [进出口]='+quotename(进出口,'''')+' then [票数] else 0 end)'
--+','+quotename(consignor)+'=sum(case when [consignor]='+quotename(consignor,'''')+' and [进出口]='+quotename(进出口,'''')+' then [利润] else 0 end)'
from #temp group by [进出口],consignor
Select @s1=@s1+','+quotename(consignor)+'=sum(case when [consignor]='+quotename(consignor,'''')+' and [进出口]='+quotename(进出口,'''')+' then [利润] else 0 end)'
--+','+quotename(consignor)+'=sum(case when [consignor]='+quotename(consignor,'''')+' and [进出口]='+quotename(进出口,'''')+' then [利润] else 0 end)'
from #temp group by [进出口],consignor
exec('select [进出口],''票数'' '+@s+' from #temp group by [进出口] union all select [进出口],''利润'''+@s1+' from #temp group by [进出口] order by 进出口 ')/*
进出口 青岛川汇国际物流有限公司 青岛正乐食品有限公司 青岛中远国际货运有限公司 青岛中远国际货运有限公司济南分公司 万华化学集团股份有限公司 青岛正乐食品有限公司 青岛中远国际货运有限公司
---- ---- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
0 利润 5394.7200 3803.2800 72484.7200 2025.2000 653.4400 0.0000 0.0000
0 票数 56.0000 60.0000 253.0000 18.0000 8.0000 0.0000 0.0000
1 利润 0.0000 0.0000 0.0000 0.0000 0.0000 1160.1600 5873.3600
1 票数 0.0000 0.0000 0.0000 0.0000 0.0000 60.0000 253.0000*/[/code]
1、行转列用聚合+case when就好了
2、列转行用unpivot
一般反复行列变换的东西都可以用这两个技巧1句话搞定
declare @s nvarchar(MAX)
DECLARE @s1 NVARCHAR(max)
set @s=''
SET @s1=''
Select @s=@s+','+quotename(consignor)+'=MAX(case when [consignor]='+quotename(consignor,'''')+' and [进出口]='+quotename(进出口,'''')+' then [票数] else 0 end)'
--+','+quotename(consignor)+'=sum(case when [consignor]='+quotename(consignor,'''')+' and [进出口]='+quotename(进出口,'''')+' then [利润] else 0 end)'
from #temp group by [进出口],consignor
Select @s1=@s1+','+quotename(consignor)+'=MAX(case when [consignor]='+quotename(consignor,'''')+' and [进出口]='+quotename(进出口,'''')+' then [利润] else 0 end)'
--+','+quotename(consignor)+'=sum(case when [consignor]='+quotename(consignor,'''')+' and [进出口]='+quotename(进出口,'''')+' then [利润] else 0 end)'
from #temp group by [进出口],consignor
exec('select [进出口],''票数'' '+@s+' from #temp group by [进出口] union all select [进出口],''利润'''+@s1+' from #temp group by [进出口] order by 进出口 ')
(
Consignor varchar(100),
票数 int,
应收 decimal(18,4),
应付 decimal(18,4),
利润 decimal(18,4),
进出口 varchar(1)
)
INSERT INTO #temp
--Consignor 票数 应收 应付 利润 进出口
select '青岛川汇国际物流有限公司', 28 ,6851.0000, 4153.6400, 2697.3600, '0'
union all
select '青岛正乐食品有限公司' ,30, 2438.0000, 536.3600, 1901.6400, '0'
union all
select '青岛正乐食品有限公司', 30 ,720.0000 ,139.9200 ,580.0800 ,'1'
UNION ALL
select '万华化学集团股份有限公司', 8, 840.0000, 186.5600, 653.4400, '0'
union all
select '青岛中远国际货运有限公司' ,253 ,98838.0000 ,26353.2800 ,72484.7200 ,'0'
union all
select '青岛中远国际货运有限公司' ,253, 6020.0000, 146.6400, 5873.3600 ,'1'
union all
select '青岛中远国际货运有限公司济南分公司', 18 ,2398.3200, 373.1200 ,2025.2000 ,'0'declare @s nvarchar(MAX)
DECLARE @s1 NVARCHAR(max)
set @s=''
SET @s1=''
Select @s=@s+','+quotename(consignor)+'=MAX(case when [consignor]='+quotename(consignor,'''')+' and [进出口]='+quotename(进出口,'''')+' then [票数] else 0 end)'from #temp group by [进出口],consignor
Select @s1=@s1+','+quotename(consignor)+'=MAX(case when [consignor]='+quotename(consignor,'''')+' and [进出口]='+quotename(进出口,'''')+' then [利润] else 0 end)'from #temp group by [进出口],consignor
exec('select [进出口],''票数'' '+@s+' from #temp group by [进出口] union all select [进出口],''利润'''+@s1+' from #temp group by [进出口] order by 进出口 ')/*
*/