分列顯示

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


*/