分列顯示

use test
go

--生成測試臨時表#
if not object_id('Tempdb..#')is null
    
drop table #

select 
    
top 35 
    
[Name]=cast[Name] as nvarchar(50)) 
into # 
from 
    syscolumns  
where 
    Name
>''

-----35行分6列顯示
select 
    
*
from
    (
select 
        Name,
        
[Ntile],
        
[Row]=row_number()over(partition by [Ntile] order by [Ntile])
    
from 
        (
select 
        
*,
        
[Ntile]=Ntile(6)over(order by Name) 
        
from #)T
        )Tmp
pivot
        (
max(Name) for [Ntile] in([1],[2],[3],[4],[5],[6])
    )Tmp2
/*
Row         1                    2               3                  4          5               6        
----------- -------------------- --------------- ------------------ ---------- --------------- --------------- 
1           base_schema_ver      id              refdate            sysstat    xtype           impid
2           cache                indexdel        replinfo           type       dpages          indid
3           category             info            schema_ver         uid        first           keycnt
4           crdate               instrig         seltrig            updtrig    FirstIAM        keys
5           deltrig              name            stats_schema_ver   userstat   groupid         lockflags
6           ftcatid              parent_obj      status             version    id              NULL

(6 個資料列受到影響)


*/


 
posted on 2007-11-29 09:14  中國風  阅读(176)  评论(0编辑  收藏  举报