复杂的汇总统计

[yuhouqingchen2020]

表结构如下：
name qname heji
A 永久 10
A 12个月 20
A 6个月 30
A 3个月 40
A 永久 15
A 12个月 6
A 6个月 5

B 永久 20
B 12个月 30
B 6个月 40
B 3个月 50
B 永久 1

想要的显示结果
name 永久 12个月 6个月 3个月
A 25 26 35 40
A 21 30 40 50

[听雨停了]

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#tab') is null
	drop table #tab
Go
Create table #tab([name] nvarchar(21),[qname] nvarchar(24),[heji] int)
Insert #tab
select N'A',N'永久',10 union all
select N'A',N'12个月',20 union all
select N'A',N'6个月',30 union all
select N'A',N'3个月',40 union all
select N'A',N'永久',15 union all
select N'A',N'12个月',6 union all
select N'A',N'6个月',5 union all
select N'B',N'永久',20 union all
select N'B',N'12个月',30 union all
select N'B',N'6个月',40 union all
select N'B',N'3个月',50 union all
select N'B',N'永久',1
Go
Select * from #tab
--测试数据结束

DECLARE @name VARCHAR(max),@sql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+qname+']'  from #tab for xml PATH('')),1,1,'')
set @sql ='SELECT * from (select name,qname,sum(heji) as heji from #tab group by name,qname) a pivot(max(heji)for qname in('+@name+'))a'
EXEC( @sql)

[RINK_1]

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(NAME VARCHAR(10),
 QNAME NVARCHAR(10),
 HEJI INT)

INSERT INTO #T
SELECT 'A','永久',10 UNION ALL
SELECT 'A','12个月',20 UNION ALL
SELECT 'A','6个月',30 UNION ALL
SELECT 'A','3个月',40 UNION ALL
SELECT 'A','永久',15 UNION ALL
SELECT 'A','12个月',6 UNION ALL
SELECT 'A','6个月',5 UNION ALL
SELECT 'B','永久',20 UNION ALL
SELECT 'B','12个月',30 UNION ALL
SELECT 'B','6个月',40 UNION ALL
SELECT 'B','3个月',50 UNION ALL
SELECT 'B','永久',1
GO 

DECLARE @SQL NVARCHAR(1000)

SELECT @SQL=ISNULL(@SQL+',','')+'['+QNAME+']' 
FROM (SELECT QNAME FROM #T GROUP BY QNAME) AS A

SET @SQL='SELECT * FROM (SELECT NAME,QNAME,SUM(HEJI) AS HEJI FROM #T GROUP BY NAME,QNAME) A 
          PIVOT (MAX(HEJI) FOR QNAME IN ('+@SQL+')) B'

EXEC(@SQL)