SQL Server 列转行应用（表中不同数据类型的列转行）

我想将图1的数据，转换为图二的数据

图一

图二​
​​​​

代码如下：

创建一个全局临时表，用来存储转换后的数据，

通过系统表syscolumns，查询该表中有多少种数据类型，每种数据类型作为一次转换，用unpivot不能转换数据类型不同的类型，就算全部是varchar类型，精度大小不一致，也是不能转换，我们以下语句，查看该表字段的详细信息，如图3

select * from syscolumns where ID=object_id('tb')

图三

 

分析该数据（Xtype表示字段的详细类型分类例如（varchar和nvarchar也区分开，区别是varchar（2）物理存储占用2个字节【length】，可以存储一个汉字，2个英文字母， 【prec】该列的精度级别一致(即存储字符的位数)。nvarchar(2),物理存储占用4个字节，可以存储2个汉字，4个英文字母，物理存储是精度的2倍））和表中字段的类型。【scale】表示该列的小数位数。

【userType】，【type】是系统表systypes中用户定义的类型。

分析systypes表，发现字段【Xusertype】可以唯一识别一个类型，其他有的不同类型，有相同值。

select Xusertype,COUNT(Xusertype)
from systypes
--where  --Xusertype=0
group by Xusertype

所以可以用【Xusertype】，【prec】，【scale】分组，可以获得该表的不同类型

 

CREATE TABLE tb(姓名 VARCHAR(10),语文 INT,数学 INT,物理 INT,专业 varchar(30),性别 varchar(2),电话 nvarchar(30),地址 nvarchar(100),平均分 float ,文科优势 numeric(5,2),理科优势 numeric(5,3))

INSERT INTO tb VALUES('张三',74,83,93,'计算机','女','0145698','llala',88.7,0.86,0.78)

INSERT INTO tb VALUES('李四',74,84,94,'电子','男','4562178','yyyyy',89.5,0.75,0.86)

go 

if OBJECT_ID('tempdb..##temp_table') is not null        
drop table ##temp_table

if OBJECT_ID('tempdb..#temp_Column') is not null        
drop table #temp_Column

create table ##temp_table(姓名 varchar(10),列名 varchar(10),值 varchar(20))
declare @Xusertype int,@prec int, @scale int,@i int
DECLARE @sql NVARCHAR(4000)

select Xusertype,isnull(prec,0) as prec ,isnull(scale,0) as scale 
into #temp_Column
from syscolumns where ID=object_id('tb') AND Name NOT IN('姓名')
group by Xusertype,prec,scale

while(select COUNT(1) from #temp_Column)>0 
begin
select @Xusertype =Xusertype ,@prec =prec , @scale= scale from #temp_Column 

	set @sql=null
	SELECT @sql=isnull(@sql+',','')+quotename(Name)
	FROM syscolumns
	WHERE ID=object_id('tb')AND Name NOT IN('姓名')
	and Xusertype =@Xusertype and isnull(prec,0)=@prec and isnull(scale,0)=@scale 

	SET @sql='insert into ##temp_table([姓名],[列名],[值]) '+
	' select 姓名,cast([列名] as varchar(10)) as [列名],'+
	' cast([值] as varchar(50)) as [值] from tb'+
	' unpivot ([值] for [列名] in('+@sql+'))b'

	print @sql
	exec(@sql)
	
	delete from #temp_Column where  Xusertype=@Xusertype
	 and prec =@prec and scale=@scale  

end

列转行在程序开发中有很多应用，有不同意见或见解的朋友，欢迎留言讨论