SQL行转列+动态拼接SQL

数据源
Name	AreaName	qty	Specific
叶玲	1	60	1
叶玲	2	1	1
叶玲	6	1	0
叶玲	7	5	0
叶玲	8	1	1

 

显示效果：

Name	1	2	8	其它	总数
叶玲	60	1	1	6	68

规则：

Specific=1的要单独统计，Specific=0的合并统计

--> 测试数据：#tb IF OBJECT_ID('tempdb.dbo.#tb') IS NOT NULL DROP TABLE #tb GO CREATE TABLE #tb([Name] VARCHAR(4),[AreaName] INT,[qty] INT,[Specific] INT) INSERT #tb SELECT '叶玲',1,60,1 UNION ALL SELECT '叶玲',2,1,1 UNION ALL SELECT '叶玲',6,1,0 UNION ALL SELECT '叶玲',7,5,0 UNION ALL SELECT '叶玲',8,1,1 --------------开始查询--------------------------

SELECT * FROM #tb AS T

declare @sql varchar(max)

select @sql=isnull(@sql+','+CHAR(13),'')+'sum(case when [AreaName]='+LTRIM([AreaName])+' then [qty] else 0 end) as '+QUOTENAME([AreaName]) from #tb WHERE [Specific]=1 GROUP BY [AreaName]

select @sql=@sql+','+CHAR(13)+'sum(case when [Specific]=0 then [qty] else 0 end) as '+QUOTENAME('其它') SELECT @sql='select [Name],'+@sql+',sum([qty]) as [总数] from #tb group by Name'

EXEC(@sql)