SQLserver行转列与列转行

转载

mb5ff58fc86bda8 2015-12-18 16:46:00

行表：

行表
姓名	属性	属性值
JACK	身高	180
JACK	体重	80
JACK	年龄	27
TOM	身高	164
TOM	体重	59
TOM	年龄	20

列表：

列表
姓名	身高	年龄	体重
JACK	180	27	80
TOM	164	20	59

行转列就是将行表转换为列表，反之为列转行。

--==================================================================
----------------------------行转列----------------------------------
--==================================================================
-------------------------建立测试表
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)