我在行列转化时是这么写的DECLARE @sql VARCHAR(8000)
IF (0<>(SELECT COUNT(1) FROM #TEMP2))
BEGIN
set @sql = 'select EmployeeCode '
select @sql = @sql + ' , MAX(case a when ''' + a + ''' then ProjectName else '''' end) [' + a + ']'
from (select distinct a from #TEMP2) as a
set @sql = @sql + ' from #TEMP2 group by EmployeeCode ORDER BY EmployeeCode'
EXEC(@sql)
END可是我抽出来的时候结果中转换后的列名,没有按照,从小到大排序。
结果为EmployeeCode 20110711 20110718 20110502 20101122
----------------------------------------------------------
1 阿 sd mm lll得怎么修改阿,求高手指点
IF (0<>(SELECT COUNT(1) FROM #TEMP2))
BEGIN
set @sql = 'select EmployeeCode '
select @sql = @sql + ' , MAX(case a when ''' + a + ''' then ProjectName else '''' end) [' + a + ']'
from (select distinct a from #TEMP2) as a
set @sql = @sql + ' from #TEMP2 group by EmployeeCode ORDER BY EmployeeCode'
EXEC(@sql)
END可是我抽出来的时候结果中转换后的列名,没有按照,从小到大排序。
结果为EmployeeCode 20110711 20110718 20110502 20101122
----------------------------------------------------------
1 阿 sd mm lll得怎么修改阿,求高手指点
IF (0<>(SELECT COUNT(1) FROM #TEMP2))
BEGIN
set @sql = 'select EmployeeCode '
select @sql = @sql + ' , MAX(case a when ''' + a + ''' then ProjectName else '''' end) [' + a + ']'
from (select distinct a from #TEMP2 order by a ) as a
set @sql = @sql + ' from #TEMP2 group by EmployeeCode ORDER BY EmployeeCode'EXEC(@sql)
END
试下,加ORDER BY a
1楼有点问题。。修改了下。。
DECLARE @sql VARCHAR(8000)
IF (0<>(SELECT COUNT(1) FROM #TEMP2))
BEGIN
set @sql = 'select EmployeeCode '
select @sql = @sql + ' , MAX(case a when ''' + a + ''' then ProjectName else '''' end) [' + a + ']'
from (select top 100 a from #TEMP2 group by a order by a ) as a
set @sql = @sql + ' from #TEMP2 group by EmployeeCode ORDER BY EmployeeCode'print @sqlEXEC(@sql)
END
if(0<>(select count(1) from #TEMP2))
begin
set @sql='select employeeCode'
select @sql=@sql+',max(case a when'''+a+''' then projectName else "" end)['+a+']'
from (select top 100 a from #TEMP2 group by a order by a) as a
set @sql=@sql+' from #TEMP2 group by EmploYeeCode order by employeecode'
print @sql
exec(@sql)
end