SQL自连接查询

问题:
表TestColor
title   color sign
a 红 111
b 红 222
a 绿 333
c 红 444
转视图:
title 红 绿
a 111 333
b 222 null
c 444 null

 

解答: 

declare @sql nvarchar(1000)
set @sql = 'select title'

select @sql = @sql + ',sum(case color when ''' + color + ''' then sign else 0 end) ['+color+']'
from (select distinct color from TestColor) as a

select @sql = @sql + ' from TestColor group by title'

print @sql

exec (@sql)