--==================================================================

----------------------------行转列----------------------------------

--==================================================================

-------------------------建立测试表

if exists(select * from sysobjects where ID=OBJECT_ID(N'BodyInfo') AND XTYPE='U')

DROP TABLE BodyInfo

Create Table BodyInfo

(

 姓名      varchar(20),

 属性      VARCHAR(20),

属性值     int

)

------------------------插入测试数据

insert into BodyInfo

select 'JACK','身高',180 union all

select 'JACK','体重',80  union all

select 'JACK','年龄',27  union all

select 'TOM','身高',164 union all

select 'TOM','体重',59  union all

select 'TOM','年龄',20 

----------------------------------------方法一：使用静态SQL

select 姓名,

max(case 属性 when '身高' then 属性值 else 0 end) AS 身高,

max(case 属性 when '体重' then 属性值 else 0 end) As 体重,

max(case 属性 when '年龄' then 属性值 else 0 end) AS 年龄

from BodyInfo group by 姓名

----------------------------------------方法二：使用动态SQL

DECLARE @sql varchar(1000)

set @sql='select 姓名'

select @sql=@sql+',max(case 属性 when '''+属性+''' then 属性值 else 0 end) AS '+属性+''

from (select  distinct 属性 from BodyInfo) a

set @sql=@sql+' from BodyInfo group by 姓名'

--print @sql

exec(@sql)

----------------------------------------方法三：使用isnull

go

/*isnull的语法是：ISNULL ( check_expression , replacement_value )

 参数

check_expression

将被检查是否为 NULL的表达式。check_expression 可以是任何类型的。

replacement_value

在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。

返回类型

返回与 check_expression 相同的类型。

*/

declare @sql varchar(8000)

select @sql=isnull(@sql+',','')+'max(case 属性 when '''+属性+''' then 属性值 else 0 end) ['+属性+']'

from (select distinct 属性 from BodyInfo) b

set @sql='select 姓名,'+@sql+' from BodyInfo group by 姓名'

print @sql

exec(@sql)

--------------------------------------------------方法四：使用pivot

select * from  BodyInfo pivot(max(属性值)for 属性 in(身高,体重,年龄))a

--------------------------------------------------方法五：使用stuff扩展pivot多变量赋值

go

declare @sql varchar(8000)

set @sql=''  --初始化变量@sql

select @sql=@sql+','+属性 from BodyInfo group by 属性--变量多值赋值

set @sql=stuff(@sql,1,1,'')--去掉首个','

set @sql='select * from BodyInfo pivot (max(属性值) for 属性 in ('+@sql+'))a'

exec(@sql)

------------------------------------------方法六：使用isnull扩展pivot多变量赋值

go

declare @sql varchar(8000)

select @sql=isnull(@sql+',','')+属性 from BodyInfo group by 属性           

set @sql='select * from BodyInfo pivot (max(属性值) for 属性 in ('+@sql+'))a'

exec(@sql)

--==================================================================

----------------------------列转行----------------------------------

--==================================================================

--建立测试表

if exists(select * from sysobjects where id=OBJECT_ID(N'personInfo') AND XTYPE='U')

DROP TABLE personInfo

create table personInfo

(

  姓名 varchar(20),

  身高 int,

  年龄 int,

  体重 int

)

----插入测试数据

INSERT INTO personInfo

SELECT 'JACK',180,27,80 UNION ALL

SELECT 'TOM',164,20,59

-------------------------------------------------方法一：使用CASE...WHEN

select * from

(

 select 姓名,属性='身高',属性值=身高 from personInfo

 union all

 select 姓名,属性='体重',属性值=体重 from personInfo

 union all

 select 姓名,属性='年龄',属性值=年龄 from personInfo

) t

order by 姓名,case 属性 when '身高' then 1 when '体重' then 2 when '年龄' then 3 end

------------------------------------------------调用系统表

--调用系统表。

go

declare @sql varchar(8000)

select @sql=isnull(@sql+' union all ','')+' select 姓名, [属性]='

+quotename(Name,'''')+' , [属性值] = '+quotename(Name)+' from personInfo'

from syscolumns

where Name!='姓名'and ID=object_id('personInfo')--表名personInfo，不包含列名为姓名的其他列

order by colid

set @sql=@sql+' order by 姓名'

--print @sql

exec(@sql)

-----------------------------------------------使用UNPIVOT

SELECT  姓名 ,

        属性 ,

        属性值

FROM    personInfo UNPIVOT ( 属性值 FOR 属性 IN ( [身高], [体重], [年龄] ) ) t 

----------------------------------------------使用isnull扩展UNPIVOT

go

DECLARE @sql NVARCHAR(4000)

SELECT  @sql = ISNULL(@sql + ',', '') + QUOTENAME(name)

FROM    syscolumns

WHERE   id = OBJECT_ID('personInfo')

        AND name NOT IN ( '姓名' )

ORDER BY colid

SET @sql = 'select 姓名,[属性],[属性值] from personInfo unpivot ([属性] for [属性值] in(' + @sql

    + '))b'

EXEC(@sql)