用SQL语句实现二维表的行列互换
在对被审计单位进行数据处理的时候,最常见的就是对行列值进行处理,比如将行的值转换为列的值,或者将列的值转化为行的值。当记录少的时候,可以手动进行处理,当记录数多的时候,必须借助SQL语句才能得以快速便捷的实现。
笔者日前在审计中遇到一张农村低保人员表,这张表中把享受低保人员的家庭成员信息都是以列的形式呈现,而且家庭成员信息的多少各有不同,如下图所示:
这样数据不方便我们与其他的数据进行关联性分析,需要予以把所有为列的家庭成员信息转换为行数据。我们可以先设置变量,然后通过游标语句提取数据予以赋值,并插入到新表中。相关语句如下:
declare @jtcyxm1 nvarchar(255),@sfzhm1 nvarchar(255),
@jtcyxm2 nvarchar(255),@sfzhm2 nvarchar(255),
@jtcyxm3 nvarchar(255),@sfzhm3 nvarchar(255),
@jtcyxm4 nvarchar(255),@sfzhm4 nvarchar(255),
@jtcyxm5 nvarchar(255),@sfzhm5 nvarchar(255),
@jtcyxm6 nvarchar(255),@sfzhm6 nvarchar(255),
@jtcyxm7 nvarchar(255),@sfzhm7 nvarchar(255),
@jtcyxm8 nvarchar(255),@sfzhm8 nvarchar(255),
@jtcyxm9 nvarchar(255),@sfzhm9 nvarchar(255)
declare hzl_cursor cursor for
select 家庭成员姓名1,身份证号码1,家庭成员姓名2,身份证号码2,
家庭成员姓名3,身份证号码3,家庭成员姓名4,身份证号码4,
家庭成员姓名5,身份证号码5,家庭成员姓名6,身份证号码6,
家庭成员姓名7,身份证号码7,家庭成员姓名8,身份证号码8,
家庭成员姓名9,身份证号码9
from dbo.裕安区农村低保人员
open hzl_cursor
fetch next from hzl_cursor into
@jtcyxm1,@sfzhm1,@jtcyxm2,@sfzhm2,@jtcyxm3,@sfzhm3,
@jtcyxm4,@sfzhm4,@jtcyxm5,@sfzhm5,@jtcyxm6,@sfzhm6,
@jtcyxm7, @sfzhm7,@jtcyxm8,@sfzhm8,@jtcyxm9,@sfzhm9
while @@FETCH_STATUS =0
begin
if @jtcyxm1 is not null and @sfzhm1 is not null
insert into dbo.裕安区农村低保人员201702( 身份证号码,人员姓名) values(@sfzhm1,@jtcyxm1)
if @jtcyxm2 is not null and @sfzhm2 is not null
insert into dbo.裕安区农村低保人员201702( 身份证号码,人员姓名) values(@sfzhm2,@jtcyxm2)
if @jtcyxm3 is not null and @sfzhm3 is not null
insert into dbo.裕安区农村低保人员201702( 身份证号码,人员姓名) values(@sfzhm3,@jtcyxm3)
if @jtcyxm4 is not null and @sfzhm4 is not null
insert into dbo.裕安区农村低保人员201702( 身份证号码,人员姓名) values(@sfzhm4,@jtcyxm4)
if @jtcyxm5 is not null and @sfzhm5 is not null
insert into dbo.裕安区农村低保人员201702( 身份证号码,人员姓名) values(@sfzhm5,@jtcyxm5)
if @jtcyxm6 is not null and @sfzhm6 is not null
insert into dbo.裕安区农村低保人员201702( 身份证号码,人员姓名) values(@sfzhm6,@jtcyxm6)
if @jtcyxm7 is not null and @sfzhm7 is not null
insert into dbo.裕安区农村低保人员201702( 身份证号码,人员姓名) values(@sfzhm7,@jtcyxm7)
if @jtcyxm8 is not null and @sfzhm8 is not null
insert into dbo.裕安区农村低保人员201702( 身份证号码,人员姓名) values(@sfzhm8,@jtcyxm8)
if @jtcyxm9 is not null and @sfzhm9 is not null
insert into dbo.裕安区农村低保人员201702( 身份证号码,人员姓名) values(@sfzhm9,@jtcyxm9)
fetch next from hzl_cursor into
@jtcyxm1,@sfzhm1,@jtcyxm2,@sfzhm2,@jtcyxm3,@sfzhm3,
@jtcyxm4,@sfzhm4,@jtcyxm5,@sfzhm5,@jtcyxm6,@sfzhm6,
@jtcyxm7,@sfzhm7,@jtcyxm8,@sfzhm8,@jtcyxm9,@sfzhm9
end
close hzl_cursor
deallocate hzl_cursor
然后将新表中的身份证号为空的记录予以删除,相关SQL语句如下:
delete from dbo.裕安区农村低保人员201702 where len([身份证号码])<1
然后查询下新表就可以看到自己想要的结果了,可以与其他数据进行关联分析。相关SQL语句与结果如下:
select * from dbo.裕安区农村低保人员201702