create table TABLE1(
employees varchar(10), unitname varchar(10), zhiwu varchar(10))
goinsert table1 select '张三','拖一','船长'
union all select '李四','拖一','水手'
union all select '王五','拖二','大副'
union all select '姚六','拖三','副船长'
union all select '黄七','拖八','水手'
union all select '毛八','拖十','水手长'
go
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',max(case unitname when '''+unitname+''' then employees else '''' end) ['+unitname+']'
from (select distinct unitname from table1 ) as a order by unitname
print @sql --把@sql打印出来你就明白的啦
set @sql = 'select zhiwu' +@sql+' from table1 group by zhiwu'
exec(@sql)我把上面这个段代码改了下,你执行看看就明白的啦
employees varchar(10), unitname varchar(10), zhiwu varchar(10))
goinsert table1 select '张三','拖一','船长'
union all select '李四','拖一','水手'
union all select '王五','拖二','大副'
union all select '姚六','拖三','副船长'
union all select '黄七','拖八','水手'
union all select '毛八','拖十','水手长'
go
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',max(case unitname when '''+unitname+''' then employees else '''' end) ['+unitname+']'
from (select distinct unitname from table1 ) as a order by unitname
print @sql --把@sql打印出来你就明白的啦
set @sql = 'select zhiwu' +@sql+' from table1 group by zhiwu'
exec(@sql)我把上面这个段代码改了下,你执行看看就明白的啦
from (select distinct unitname from table1 ) as a order by unitname"你从打印的结果中就能看出,其实是某个字段的所有记录的字符串累加,是个循环来的