SQLserver行转列与列转行
时间：2021-07-01 10:21:17
帮助过：5人阅读
==================================================================
----------------------------行转列----------------------------------
--==================================================================
-------------------------建立测试表
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)
SQLserver行转列与列转行
标签：
