我想将图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
列转行在程序开发中有很多应用,有不同意见或见解的朋友,欢迎留言讨论