第四种行转列

最新推荐文章于 2024-07-22 17:18:58 发布

weixin_30484739

最新推荐文章于 2024-07-22 17:18:58 发布

阅读量70

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/qanholas/archive/2012/05/31/2528386.html

--动态处理

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

转载于:https://www.cnblogs.com/qanholas/archive/2012/05/31/2528386.html