create table T1
(
name varchar(20),
tcode varchar(20),
total decimal(18,2)
)
insert T1(name,tcode,total) values
('张三','瓷砖','10000'),
('张三','地暖','20000'),
('张三','空调','30000'),
('李四','空调','40000'),
('李四','品牌家具','50000'),
('李四','地漏','60000')
--实现效果
姓名 瓷砖 地暖 空调 品牌家具 地漏
张三 10000 20000 30000 0 0
李四 0 0 40000 50000 60000
要点：1.根据客户进行汇总；2.Tcode中出现的不重复大类都要在列中呈现
------解决思路----------------------
------解决思路----------------------
declare @s varchar(max)
select @s=isnull(@s+',','')+'sum(case when tcode='''+tcode+''' then total else 0 end) as ['+tcode+']' from T1 group by tcode
set @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拼接。