SQL Server中动态列的处理方法

概要

我们在进行业务系统开发时候，经常碰上这种情况，即因为业务信息的不确定性，导致在数据库设计时候，无法确定所有的数据表栏位。用户需要在系统中动态创建一些栏位，以支持其业务运转。

本文以一个收集参加会议人员信息的例子，来介绍动态列或动态栏位在T-SQL中的处理方法。

业务数据

对于参会人员信息，根据会议类型的不同，存在不同的需求，例如国际会议，有外国专家参加，需要护照号，本国参会人，则需要身份证号。对于非国际会议，则不需要护照号这样的信息，如果没有住宿需求，身份证号也不需要，诸如此类不确定的需求在本实例中非常多。

基于以上业务需求，我们的数据库表设计如下：

1. 会议信息表，记录会议主题，主办方，会议场地等基本信息，由于该表和动态列的关系不大，本文不再过多赘述。
2. 参会人员基本信息表，包括人员Id和姓名。

 table t_users (
	Id int primary key identity(1,1),
	Name nvarchar(20) not null,
)

3. 参会人员收集表，记录参加会议人员的信息，如出发时间，到达时间，航班信息，火车车次信息，住宿要求等信息

create table t_participants (
	Id int primary key identity(1,1),
	UserId int not null,
	MeetId int not null,
	StartDate DateTime2 not null,
	ArrivalDate DateTime2 not null,
	TrainInfo nvarchar(50) default '',
	FlightInfo nvarchar(50) default '',
	AccomodationComment  nvarchar(50) default ''
)

4. 参会人员信息表，记录参会人员不确定的那部分栏位信息。包括参会人员Id，会议Id， 动态列名称，如护照号，身份证号，动态列对应的值，如护照号或身份证号等。

create table t_participants_info(
	Id int primary key identity(1,1),
	UserId int not null,
	MeetId int not null,
	Title nvarchar(50) not null,
	Val nvarchar(50) not null,
)

表格数据初始化详见附录。本文以身份证号和护照号作为动态创建列使用。

实现目标

通过查询获取每个参会人员的信息，包括基本会议信息和动态创建的护照号和身份证号信息。

代码实现

实现该目标的关键是对参会人员信息表进行行列转置，将通过行存储的护照号和身份证号转换为对应的护照号列和身份证号列。

Pivot函数实现转置

转置关键代码：

select pvt.UserId, pvt.MeetId, pvt.Passport, pvt.IDCard from
	(select 
			UserId, MeetId,Title,Val
			from t_participants_info
	) info
	pivot (max(info.Val) for info.Title in ([Passport],[IDCard])) as pvt

1. 因为不是每个参会人员都有护照或身份证，所以对转制后的护照和身份证列的数值进行置空检查，如果为空，则置为’’
2. 由于参会人员信息表记录了每个会议的每位参会人员信息，所以要通过用户Id和会议Id进行分组。
3. 注意pivot必须配合子查询使用。

获取参会人员全部信息代码：

select 
p.UserId, u.Name, p.MeetId, p.StartDate, p.ArrivalDate, p.TrainInfo, p.FlightInfo, p.AccomodationComment, 
dtl.Passport,dtl.IDCard
from t_participants p
left join t_users u
on u.Id = p.UserId
left join (
	select pvt.UserId, pvt.MeetId, pvt.Passport, pvt.IDCard from
	(select 
			UserId, MeetId,Title,Val
			from t_participants_info
	) info
	pivot (max(info.Val) for info.Title in ([Passport],[IDCard],)) as pvt
) as dtl on dtl.MeetId = p.MeetId and dtl.UserId = p.UserId

group by实现转置

转置关键代码：

select 
		info.MeetId, info.UserId,
		max(case info.Title when 'Passport' then info.Val else '' end) as  Passport,
		max(case info.Title when 'IDCard' then info.Val else '' end) as  IDCard
from t_participants_info info
group by info.MeetId, info.UserId

对需要转置的护照号和身份证号添加聚合函数max的处理，从而逐行抽取出身份证号列和护照号列。

获取参会人员全部信息代码：

select 
	p.UserId, u.Name, p.MeetId, p.StartDate, p.ArrivalDate, p.TrainInfo, p.FlightInfo, p.AccomodationComment,
	dtl.Passport,dtl.IDCard 
from t_participants p
left join t_users u
on u.Id = p.UserId
left join (
	select 
		info.MeetId, info.UserId,
		max(case info.Title when 'Passport' then info.Val else '' end) as  Passport,
		max(case info.Title when 'IDCard' then info.Val else '' end) as  IDCard
	from t_participants_info info
	group by info.MeetId, info.UserId
) as dtl on dtl.MeetId = p.MeetId and dtl.UserId = p.UserId

代码改进

上述代码有一个很大的缺陷，就是现实情况下，动态栏位如护照号或身份证号，开发者是不知道的，需要根据用户的输入动态获取，而不是通过硬编码实现。

我们采用动态SQL对上述代码进行改进，以删除代码中的硬编码。

Pivot函数实现的改进

对于通过Pivot函数的解决方案，需要替换的硬编码3处，如截图所示的标黄区域：

declare @pivotForCols nvarchar(100)
declare @sql nvarchar(800)
declare @isNullColTemplate nvarchar(500)
declare @tmpCol nvarchar(20);
declare @isNullCols nvarchar(500);
declare @pivotCols nvarchar(100)

set @pivotForCols = stuff((select distinct ',' + quotename(title) from t_participants_info for xml path('')),1,1,'')
set @pivotCols = stuff((select distinct ', dtl.' +  title from t_participants_info for xml path('')),1,1,'')
set @isNullColTemplate = ' ,isnull(pvt.$COL,'''') as $COL'
set @isNullCols= ''

declare cursorCol cursor fast_forward for 
select distinct title from t_participants_info t where t.MeetId = 1 
open cursorCol;
fetch next from cursorCol into @tmpCol
while @@FETCH_STATUS = 0
begin
	set @isNullCols = @isNullCols + replace(@isNullColTemplate,'$COL',@tmpCol)
	fetch next from cursorCol into @tmpCol
end
close cursorCol;
deallocate cursorCol; 

set @sql = N'

select p.UserId, u.Name, p.MeetId, p.StartDate, p.ArrivalDate, p.TrainInfo, p.FlightInfo, p.AccomodationComment,
$PIVOTCOLS
from t_participants p
left join t_users u
on u.Id = p.UserId

left join (
	select pvt.UserId, pvt.MeetId $ISNULLCOLS from
	(select 
			UserId, MeetId,Title,Val
			from t_participants_info
	) info
	pivot (max(info.Val) for info.Title in ($PIVOTFORCOLS)) as pvt


) as dtl on dtl.MeetId = p.MeetId and dtl.UserId = p.UserId'

set @sql = replace(@sql,'$PIVOTCOLS',@pivotCols)
set @sql = replace(@sql,'$ISNULLCOLS',@isNullCols)
set @sql = replace(@sql,'$PIVOTFORCOLS',@pivotForCols)

print @sql
exec sp_executesql  @sql

group by实现转置的改进

对于通过group by的解决方案，需要替换的硬编码有两处，如截图所示的标黄区域：

declare @sql nvarchar(800);
declare @maxTemplate nvarchar(100);
declare @maxCols nvarchar(500);
declare @tmpCol nvarchar(50);
declare @pivotCols nvarchar(500)
set  @maxTemplate = ' ,max(case info.Title when ''$COL'' then info.Val else '''' end) as  $COL '
set @maxCols = ''
set @pivotCols = stuff((select distinct ', dtl.' +  title from t_participants_info for xml path('')),1,1,'')
declare cursorCol cursor fast_forward for 
select distinct title from t_participants_info t where t.MeetId = 1 
open cursorCol;
fetch next from cursorCol into @tmpCol
while @@FETCH_STATUS = 0
begin
	print @tmpCol
	set @maxCols = @maxCols + replace(@maxTemplate,'$COL',@tmpCol)
	fetch next from cursorCol into @tmpCol
end
close cursorCol;
deallocate cursorCol; 
print @maxCols

set @sql = N'
select p.UserId, u.Name, p.MeetId, p.StartDate, p.ArrivalDate, p.TrainInfo, p.FlightInfo, p.AccomodationComment,
$PIVOTCOLS 
from t_participants p
left join t_users u
on u.Id = p.UserId

left join (
	select 
		info.MeetId, info.UserId
		$MAXCOL
	from t_participants_info info
	group by info.MeetId, info.UserId
) as dtl on dtl.MeetId = p.MeetId and dtl.UserId = p.UserId'

set @sql = replace(@sql,'$PIVOTCOLS',@pivotCols)
set @sql = replace(@sql,'$MAXCOL',@maxCols)
print @sql
exec sp_executesql  @sql

总结

无论我们选择哪种方法支持用户自定义列（动态列）的查询，基本思路都是我们通过动态SQL，将用户自定义列的内容根据用户的实际输入，实时获取，再用获取的内容替换掉原有硬编码部分的占位符。

在用户新增自定列时候，我们不需要对已有代码进行任何修改。例如用户新增自定列联系电话TEL，新增数据如下：

insert into t_participants_info values 
(1,1,'TEL', '12345678')
(2,1,'TEL', '34567889'),
(3,1,'TEL', '2344354353'),
(4,1,'TEL', '222445435345')

此时我们运行上面任何一个改进脚本，可以直接将用户电话号码显示出来。

附录

if object_id('t_users') is not null
	drop table t_users
create table t_users (
	Id int primary key identity(1,1),
	Name nvarchar(20) not null,
)
if object_id('t_participants') is not null
	drop table t_participants
create table t_participants (
	Id int primary key identity(1,1),
	UserId int not null,
	MeetId int not null,
	StartDate DateTime2 not null,
	ArrivalDate DateTime2 not null,
	TrainInfo nvarchar(50) default '',
	FlightInfo nvarchar(50) default '',
	AccomodationComment  nvarchar(50) default ''
)
if object_id('t_participants_info') is not null
	drop table t_participants_info
create table t_participants_info(
	Id int primary key identity(1,1),
	UserId int not null,
	MeetId int not null,
	Title nvarchar(50) not null,
	Val nvarchar(50) not null,
)

insert into t_users values ('A'),('B'),('C'),('D')

insert into t_participants values 
(1,1,'2020-10-01 12:30', '2020-10-02 12:30', 'N/A', 'F1111', 'Victory Hotel Single Room'),
(2,1,'2020-10-01 08:30', '2020-10-02 12:30', 'K-1234', 'N/A', 'Victory Hotel Multi-person Room'),
(3,1,'2020-10-01 09:30', '2020-10-02 12:30', 'N/A', 'F2222', 'Victory Hotel Single Room'),
(4,1,'2020-10-02 12:30', '2020-10-02 13:30', 'K-1234', 'N/A', 'Victory Hotel Multi-person Room')

insert into t_participants_info values 
(1,1,'Passport', 'A12345678'),
(2,1,'IDCard', '456789'),
(3,1,'Passport', 'B12345888'),
(4,1,'IDCard', '78901231')