--列转行
declare @sql varchar(8000)
set @sql='select ys '
select @sql=@sql+', sum(case when [ms]='''+ ms +''' then sl else 0 end) as ['+ ms +']' FROM (select distinct MS from xskd_mx) as a
select @sql=@sql+', sum(isnull(sl,0)) as [hj] from xskd_mx group by ys'
exec(@sql)
--行转列
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select sh, ys, [ms] = ' + quotename(ms , '''') + ' , [sl] = ' + quotename(ms) + ' from khdd_zb'
from ms
exec(@sql )
--也就是说,quotename 使函数中的输入成为一个有效的标识符。
--还有一点就是quotename函数有几种写法:
-- quotename('aa') 生成的有效的标识符为 [aa]
-- quotename('aa','') 生成的有效的标识符为 [aa]
-- quotename('aa','''') 生成的有效的标识符为 'aa'