--行列互转

/******************************************************************************************************************************************************

以学生成绩为例子，比较形象易懂



整理人：中国风(Roy)



日期:2008.06.06

******************************************************************************************************************************************************/



--1、行互列

--> --> (Roy)生成測試數據

 

if not object_id('Class') is null

    drop table Class

Go

Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)

Insert Class

select N'张三',N'语文',78 union all

select N'张三',N'数学',87 union all

select N'张三',N'英语',82 union all

select N'张三',N'物理',90 union all

select N'李四',N'语文',65 union all

select N'李四',N'数学',77 union all

select N'李四',N'英语',65 union all

select N'李四',N'物理',85 

Go

--2000方法:

动态:



declare @s nvarchar(4000)

set @s=''

Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'

from Class group by[Course]

exec('select [Student]'+@s+' from Class group by [Student]')





生成静态:



select 

    [Student],

    [数学]=max(case when [Course]='数学' then [Score] else 0 end),

    [物理]=max(case when [Course]='物理' then [Score] else 0 end),

    [英语]=max(case when [Course]='英语' then [Score] else 0 end),

    [语文]=max(case when [Course]='语文' then [Score] else 0 end) 

from 

    Class 

group by [Student]



GO

动态:



declare @s nvarchar(4000)

Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]

exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')



生成静态:

select * 

from 

    Class 

pivot 

    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b



生成格式:

/*

Student 数学          物理          英语          语文

------- ----------- ----------- ----------- -----------

李四      77          85          65          65

张三      87          90          82          78



(2 行受影响)

*/



------------------------------------------------------------------------------------------

go

--加上总成绩(学科平均分)



--2000方法:

动态:



declare @s nvarchar(4000)

set @s=''

Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'

from Class group by[Course]

exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))



生成动态:



select 

    [Student],

    [数学]=max(case when [Course]='数学' then [Score] else 0 end),

    [物理]=max(case when [Course]='物理' then [Score] else 0 end),

    [英语]=max(case when [Course]='英语' then [Score] else 0 end),

    [语文]=max(case when [Course]='语文' then [Score] else 0 end),

    [总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))

from 

    Class 

group by [Student]



go



--2005方法:



动态:



declare @s nvarchar(4000)

Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号

exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a 

pivot (max([Score]) for [Course] in('+@s+'))b ')



生成静态:



select 

    [Student],[数学],[物理],[英语],[语文],[总成绩] 

from 

    (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])

pivot 

    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 



生成格式:



/*

Student 数学          物理          英语          语文          总成绩

------- ----------- ----------- ----------- ----------- -----------

李四      77          85          65          65          292

张三      87          90          82          78          337



(2 行受影响)

*/



go



--2、列转行

--> --> (Roy)生成測試數據

 

if not object_id('Class') is null

    drop table Class

Go

Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)

Insert Class

select N'李四',77,85,65,65 union all

select N'张三',87,90,82,78

Go



--2000:



动态:



declare @s nvarchar(4000)

select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all

+',[Score]='+quotename(Name)+' from Class'

from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列

order by Colid

exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序



生成静态:

select * 

from (select [Student],[Course]='数学',[Score]=[数学] from Class union all 

select [Student],[Course]='物理',[Score]=[物理] from Class union all 

select [Student],[Course]='英语',[Score]=[英语] from Class union all 

select [Student],[Course]='语文',[Score]=[语文] from Class)t 

order by [Student],[Course]



go

--2005:



动态:



declare @s nvarchar(4000)

select @s=isnull(@s+',','')+quotename(Name)

from syscolumns where ID=object_id('Class') and Name not in('Student') 

order by Colid

exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')



go

select 

    Student,[Course],[Score] 

from 

    Class 

unpivot 

    ([Score] for [Course] in([数学],[物理],[英语],[语文]))b



生成格式:

/*

Student Course Score

------- ------- -----------

李四      数学      77

李四      物理      85

李四      英语      65

李四      语文      65

张三      数学      87

张三      物理      90

张三      英语      82

张三      语文      78



(8 行受影响)

*/

 /*--------------------------------------------------

 表格旋转/转置: 



* 说明



  不支持下列数据类型: image, text, ntext, hierarchyid, geometry, geography.



  2000 好像没有 fn_varbintohexstr, 所以不支持 varbinary, binary, timestamp,

  要扩展自己写 bin2str 函数.



  2008 的 varbinary/binary 可以直接 convert, 详细参考联机/MSDN, 但懒得判断

  版本了, 一律用 fn_varbintohexstr.



  2000 内层受长度 8000 的限制, 某列数据超过 8000 长度肯定报错.

  2005/2008 检测用 varchar(max)



  轴向列转 sysname, 超过 128 截断. 其它列除有限的几个要显式转换的数据类型,

  一律用 rtrim() 隐式转换, 具体看代码.



  有处理 NULL 值, 不至于被一个 NULL 玩死.



* 作者



  这些东西毫无技术含量可言, 不敢言称作者, 以免贻笑大方.



  PS: 写着玩/不维护/不扩展, BUG 有时间就跟进无时间见谅. 相信除了长度限制的

  硬伤, 其它 BUG 的可能性不大.



--------------------------------------------------------------------------*/

CREATE PROCEDURE p_rotate

(

    @table  sysname,        -- 表/视图

    @axis   sysname = null, -- 轴, 旋转后作为字段名, 默认第1列

    @rename sysname = null, -- 重命名轴

    @style  int     = 121   -- 日期时间转换样式

)

AS



SET NOCOUNT ON



if object_id(@table) is null return -- 不废话



declare @inner varchar(8000) -- 定义内层 exec 变量

declare @first varchar(8000) -- 每行数据的第一列 即原字段名变成第1列

declare @rows  varchar(8000) -- 读取每列数据作为行数据

declare @union varchar(8000) -- 每行数据 union all

declare @max   varchar(10)

declare @type  int



select @axis = isnull(@axis, (select name from syscolumns where id=object_id(@table) and colid=1))

select @type = xtype from syscolumns where id=object_id(@table) and name=@axis



if @type in (34,35,99,240) -- image,text,ntext,hierarchyid,geometry,geography

or @@version not like '%Server 200[58]%' and @type in (165,173,189) -- varbinary,binary,timestamp

begin

    select name from systypes where xtype = @type

    return

end



select @rename = isnull(@rename, @axis), @max = case when @@version like '%Server 200[58]%' then 'max' else '8000' end



-- 构造内层 exec

select

    @inner = isnull(@inner+',','')+'@'+ltrim(colid)+' varchar('+@max+')',

    @first = isnull(@first+',','')+'@'+ltrim(colid)+'=''select ['+@rename+']='''''+name+'''''''',

    @rows = isnull(@rows,'')+char(13)+char(10)+'select @'+ltrim(colid)+'=@'+ltrim(colid)+'+'',[''+isnull('+

        case

            when @type = 189 then 'master.sys.fn_varbintohexstr(convert(binary(8),['+@axis+']))' -- timestamp

            when @type in (165,173) then 'left(master.sys.fn_varbintohexstr(['+@axis+']),128)' -- varbinary,binary

            when @type in (175,239) then 'rtrim(convert(sysname,['+@axis+']))' -- char,nchar

            when @type in (40,41,42,43,58,61) then 'convert(sysname,['+@axis+'],'+ltrim(@style)+')' -- date,time,datetime2,datetimeoffset,smalldatetime,datetime

            else 'convert(sysname,['+@axis+'])'

        end+',''NULL'')+'']=''+isnull(quotename('+

        case

            when xtype = 189 then 'master.sys.fn_varbintohexstr(convert(binary(8),['+name+']))' -- timestamp

            when xtype in (165,173) then 'master.sys.fn_varbintohexstr(['+name+'])' -- varbinary,binary

            --when xtype in (60,122) then 'convert(varchar(50),['+name+'],2)' -- money,smallmoney -- 需要精细控制类型转换这里添加

            when xtype in (40,41,42,43,58,61) then 'convert(varchar(50),['+name+'],'+ltrim(@style)+')' -- date,time,datetime2,datetimeoffset,smalldatetime,datetime

            when xtype in (98,241) then 'convert(varchar('+@max+'),['+name+'])' -- sql_variant,xml

            else 'rtrim(['+name+'])'

        end+', char(39)),''null'') from ['+@table+']',

    @union = isnull(@union+'+'' union all ''+','')+'@'+ltrim(colid)

from syscolumns

where id=object_id(@table) and name<>@axis and (xtype not in (34,35,99,165,173,189,240) or @@version like '%Server 200[58]%' and xtype not in (34,35,99,240))

order by colid



-- print/exec

exec('declare '+@inner+'

select '+@first+@rows+'

exec('+@union+')')



SET NOCOUNT OFF

--给个一列多用的

--原创不详

一张表 t 字段 team

'a'

'b',

'c',

'd'

任意组合

结果



a,b

a,c

a,d

b,c

b,d

c,d

怎么写





create table t(team varchar(1))

insert into t values('a')

insert into t values('b')

insert into t values('c')

insert into t values('d')

go



select m.team , n.team from t m, t n where m.team < n.team order by m.team



drop table t



/*

team team 

---- ---- 

a    b

a    c

a    d

b    c

b    d

c    d



（所影响的行数为 6 行）





*/