第四种行转列
--动态处理
select A.StuName,A.BZKTypeName,cast(A.BKCODE as varbinary(MAX)) even,
row_number() over (partition by StuName,BZKTypeName order by getdate()) ID
into #t1
from BKLIST A
--where StuName='林健辉'
declare @sql1 varchar(max)
declare @sql2 varchar(max)
declare @id int
declare @maxid int
select @maxid=max(id)
from #t1
set @id=1
set @sql1=''
set @sql2=''
while @id<@maxid
begin
set @sql1=@sql1+'['+CAST(@id as varchar(10))+'],'
set @sql2=@sql2+' case when '+'['+CAST(@id as varchar(10))+']'
+' IS null then '''' else CAST('+'['+CAST(@id as varchar(10))+']'
+' as varchar(100))+'','' end +'
set @id=@id+1
end
set @sql1=@sql1+'['+CAST(@id as varchar(10))+']'
set @sql2=@sql2+' case when '+'['+CAST(@id as varchar(10))+']'
+' IS null then '''' else CAST('+'['+CAST(@id as varchar(10))+']'
+' as varchar(100)) end '
--print @sql1
--print @sql2
declare @sql varchar(max)
set @sql='
select StuName,BZKTypeName,LEFT(BKCODE,LEN(BKCODE)-1) from (
SELECT StuName,BZKTypeName,
'+@sql2+'
BKCODE
FROM ( select * from #t1
) s2 PIVOT ( max(even) FOR ID IN ('+@sql1+')) as pvt
)s3
'
print @sql
exec( @sql)
-------------------------------------------------------------------
--执行的语句
select StuName,BZKTypeName,LEFT(BKCODE,LEN(BKCODE)-1) from (
SELECT StuName,BZKTypeName,
case when [1] IS null then '' else CAST([1] as varchar(100))+',' end
+ case when [2] IS null then '' else CAST([2] as varchar(100))+',' end
+ case when [3] IS null then '' else CAST([3] as varchar(100))+',' end
+ case when [4] IS null then '' else CAST([4] as varchar(100))+',' end
+ case when [5] IS null then '' else CAST([5] as varchar(100)) end
BKCODE
FROM ( select * from #t1
) s2 PIVOT ( max(even) FOR ID IN ([1],[2],[3],[4],[5])) as pvt
)s3
----------------------------------------------------------------------------------------------------------
--与XML方式的一次对比
--xml方式脚本
select B.StuName,B.BZKTypeName,left(NT,len(NT)-1) as evenNew
from (
select StuName,BZKTypeName,(
select BKCODE+','
from BKList
where StuName=A.StuName
and BZKTypeName=A.BZKTypeName
order by StuName,BZKTypeName
for
xml path('')
) as NT
from BKList A
group by StuName,BZKTypeName
) B
