SQL行列转换
是个公司采购表，想转化成如下报表，显示每个公司各采购了些什么产品：
sql语句》答案：
1 declare @sql varchar(8000)--声明一个字符串变量
2 set @sql='select CompanyName,'--开始设置语句
3 --------动态生成语句begin(开始转成列)-----
4 select @sql=@sql'sum(case when ProductName='''ProductName''' then num else 0 end)['ProductName'],'
5 from (select distinct top 100 percent ProductName from Orders order by ProductName)a
6 --------动态生成语句 end--------------------
7 set @sql =left(@sql,len(@sql)-1)' from (select CompanyName,ProductName,COUNT(*)as num
2 set @sql='select CompanyName,'--开始设置语句
3 --------动态生成语句begin(开始转成列)-----
4 select @sql=@sql'sum(case when ProductName='''ProductName''' then num else 0 end)['ProductName'],'
5 from (select distinct top 100 percent ProductName from Orders order by ProductName)a
6 --------动态生成语句 end--------------------
7 set @sql =left(@sql,len(@sql)-1)' from (select CompanyName,ProductName,COUNT(*)as num
from dbo.Orders group by
ProductName,CompanyName)a group by CompanyName'
8 print @sql --打印输出最终执行的SQL
9 exec(@sql)--执行SQL字符串
9 exec(@sql)--执行SQL字符串
