?--動態處理

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

總結

以上是生活随笔為你收集整理的第四种行转列的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。