分列顯示

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 個資料列受到影響)


*/