declare @s varchar(max)select @s=isnull(@s+',','')+'sum(case when tcode='''+tcode+''' then total else 0 end) as ['+tcode+']' from T1 group by tcodeset @s='select name as 姓名,'+@s+ ' from T1 group by name'exec(@s)
行轉列 select name, sum(case when tcode='瓷砖' then total else 0 end) as 瓷砖, sum(case when tcode='地暖' then total else 0 end) as 地暖, sum(case when tcode='空调' then total else 0 end) as 空调, sum(case when tcode='品牌家具' then total else 0 end) as 品牌家具, sum(case when tcode='地漏' then total else 0 end) as 地漏 from T1 group by name 如果tcode的數據很多或不明確,就要使用動態sql拼接。
--试试 declare @s nvarchar(4000) Select @s=isnull(@s+',','')+quotename(tcode) from T1 group by tcode --isnull(@s+',','') 去掉字符串@s中第一个逗号 print @s exec('select name,'+@s+' from (select *,total_sum=sum(T1.total)over(partition by name) from T1) a pivot (sum(total) for tcode in('+@s+'))b ')
DECLARE @SQL NVARCHAR(MAX) SET @SQL='' SELECT @SQL=@SQL+',['+tcode+']'FROM T1 GROUP BY tcode SET @SQL='SELECT * FROM T1 PIVOT(SUM(total)FOR tcode IN('+STUFF(@SQL,1,1,'')+'))P' EXEC(@SQL)
declare @s varchar(max)select @s=isnull(@s+',','')+'sum(case when tcode='''+tcode+''' then total else 0 end) as ['+tcode+']' from T1 group by tcodeset @s='select name as 姓名,'+@s+ ' from T1 group by name'exec(@s)
select name,
sum(case when tcode='瓷砖' then total else 0 end) as 瓷砖,
sum(case when tcode='地暖' then total else 0 end) as 地暖,
sum(case when tcode='空调' then total else 0 end) as 空调,
sum(case when tcode='品牌家具' then total else 0 end) as 品牌家具,
sum(case when tcode='地漏' then total else 0 end) as 地漏
from T1
group by name
如果tcode的數據很多或不明確,就要使用動態sql拼接。
--试试
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename(tcode)
from T1 group by tcode --isnull(@s+',','') 去掉字符串@s中第一个逗号
print @s
exec('select name,'+@s+' from (select *,total_sum=sum(T1.total)over(partition by name) from T1) a
pivot (sum(total) for tcode in('+@s+'))b ')
SET @SQL=''
SELECT @SQL=@SQL+',['+tcode+']'FROM T1 GROUP BY tcode
SET @SQL='SELECT * FROM T1 PIVOT(SUM(total)FOR tcode IN('+STUFF(@SQL,1,1,'')+'))P'
EXEC(@SQL)