sql server - 从一个包含列的表中获取值名称存储在另一个表中

Question

我有几张桌子。这些是表的简化版本：

      UsersTable:
      UserId FirstName LastName MiddleInit Suffix    Age   Position 
      1    John       Graham     P.        Jr.     35     Analyst II
      2    Bill       Allen      T.        III     45     Programmer I
      3    Jenny      Smith      K.                25     Systems Engineer
      4    Gina       Todd       J.                55     Analyst II

     TableTypes:
     TableTypeId TableType
      1               Names
      2               Positions
      3               Age

TableTypeId 是 TableType 中的主键，是 TableField 中的外键。

     TableFields:
     FieldId  TableTypeId FieldName Description
     1           1         FirstName    descr1
     2           1         LastName     descr2
     3           1         MiddleInit   descr3
     4           1         Suffix       descr4

FieldId 是 TableFields 中的主键，是 ModifiedUsersTable 中的外键。

我需要用 UsersTable 中的值填充 ModifiedUsersTable，因此它看起来像这样：

       ModifiedUsersTable:
    Id  UserId      FieldId  Value
    1      1          1       John      
    2      1          2       Graham
    3      1          3         P.        
    4      1          4         Jr.
    5      2          1        Bill

等等。

我只需要从 UsersTable 中获取 TableFields 表中 FieldName 列中列出的那些列。我事先不知道 TableFields 中的行数（fieldNames）或名称。 我想我应该尝试使用 PIVOT。我可以获得以逗号分隔的列名称字符串，然后动态地将它们转换为列。
但它似乎没有给我我需要的东西。如何填写 ModifiedUsersTable？ 有什么建议吗？ 谢谢！

Answer 1

数据的 DDL

create table UsersTable(
    UserId int, FirstName varchar(100), LastName varchar(100), MiddleInit varchar(100),
    Suffix varchar(100), Age int, Position varchar(100));
insert UsersTable select 
'1','John','Graham','P.','Jr.','35','Analyst II' union all select
'2','Bill','Allen','T.','III','45','Programmer I' union all select
'3','Jenny','Smith','K.',null,'25','Systems Engineer' union all select
'4','Gina','Todd','J.',null,'55','Analyst II';
create table tabletypes(
    TableTypeId int, TableType varchar(100));
insert tabletypes select
1, 'Names' union all select
2, 'Positions' union all select
3, 'Age';

create table TableFields(
    FieldId int,TableTypeId int,FieldName varchar(100),Description varchar(100));
insert TableFields select
'1','1','FirstName','descr1' union all select
'2','1','LastName','descr2' union all select
'3','1','MiddleInit','descr3' union all select
'4','1','Suffix','descr4';

create table ModifiedUsersTable(
    Id int identity, userid int, fieldid int, value varchar(max));

用于填充 ModifiedUsersTable 的脚本

declare @sql nvarchar(max)
select @sql = coalesce(@sql+ '
    union all
    ', '') + '
    select userid,' + right(FieldId,12) + ',' + quotename(fieldname) + '
    from userstable'
from tablefields
set @sql = @sql + '
    order by userid, 2'

-- print @sql -- uncomment to see the generated SQL
exec (@sql)