--动态处理

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,

  

   BKCODE

  FROM ( select *    from #t1

   ) s2  PIVOT ( max(even) FOR ID IN ()) 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