sql server 转置透视，多行变一行

转置，透视
select
销售姓名,二级部门,三级部门,四级部门
,count(distinct 合同客户名称) as 客户数,产品显示名称
into #new
from OrderChengJ_dd_update
where 成交日期 between ‘2021-07-01’ and ‘2021-08-01’
and 产品二级分类 = ‘自主开发产品’
group by 产品显示名称,销售姓名,二级部门,三级部门,四级部门

declare @sql varchar(8000)
set @sql = ‘select 销售姓名,二级部门,三级部门,四级部门’
select @sql = @sql + ‘,isnull (sum(case 产品显示名称 when ‘’’+产品显示名称+’’’ then 客户数 end),0) as [’+产品显示名称+’]’
from (select distinct 产品显示名称 from #new ) as a
select @sql = @sql+’ from #new group by 销售姓名,二级部门,三级部门,四级部门’
exec(@sql)

多行变一行

with new as (
select count(distinct 合同客户名称) as 客户数,
sum(成交合同金额) as 成交金额,
产品显示名称,
销售姓名,二级部门,三级部门,四级部门
,concat(产品显示名称,’-’,count(distinct 合同客户名称),‘单’) as 产品数量
from OrderChengJ_dd
where 成交日期 between ‘2021-07-01’ and ‘2021-08-01’
and 产品二级分类 = ‘自主开发产品’
group by 产品显示名称,销售姓名,二级部门,三级部门,四级部门
)
select 销售姓名,二级部门,三级部门,四级部门,sum(客户数) as 客户数
, stuff((select ‘,’ + 产品数量 from new where 销售姓名 = a.销售姓名 for xml path(’’)), 1, 1, ‘’) as hobby
from new as a
group by 销售姓名,二级部门,三级部门,四级部门
order by 客户数 desc