将透视表列取消透视成键值行 [英] Unpivot dynamic table columns into key value rows

问题描述

我需要解决的问题是将数据从具有许多动态字段的一个表转移到其他结构化键值表中. 第一个表来自另一个系统的数据导出，具有以下结构(它可以具有任何列名和数据): [UserID]，[FirstName]，[LastName]，[Email]，[您今天过得如何]，[您想接收每周新闻简报]，[确认您年满18岁] ...

The problem that I need to resolve is data transfer from one table with many dynamic fields into other structured key value table. The first table comes from a data export from another system, and has the following structure ( it can have any column name and data): [UserID],[FirstName],[LastName],[Email],[How was your day],[Would you like to receive weekly newsletter],[Confirm that you are 18+] ...

第二个表是我要放置数据的位置，它具有以下结构: [UserID uniqueidentifier]，[QuestionText nvarchar(500)]，[问题答案nvarchar(max)]

The second table is where I want to put the data, and it has the following structure: [UserID uniqueidentifier],[QuestionText nvarchar(500)],[Question Answer nvarchar(max)]

我看到了许多显示如何取消透视表的示例，但是我的问题是我不知道表1将具有哪些列.我可以以某种方式动态取消对第一个表的透视，所以无论它具有什么列，它都将转换为键值结构并将数据导入到第二个表中.

I saw many examples showing how to unpivot table, but my problem is that I dont know what columns the Table 1 will have. Can I somehow dynamically unpivot the first table,so no matter what columns it has, it is converted into a key-value structure and import the data into the second table.

非常感谢您的帮助.

推荐答案

在不知道列的情况下，您无法在一个查询中进行透视或不透视.

You can't pivot or unpivot in one query without knowing the columns.

在具有特权的情况下，您可以做的是查询sys.columns以获取源表的字段名，然后动态构建一个不可透视的查询.

What you can do, assuming you have privileges, is query sys.columns to get the field names of your source table then build an unpivot query dynamically.

--Source table
create table MyTable (
    id int,
    Field1 nvarchar(10),
    Field2 nvarchar(10),
    Field3 nvarchar(10)
);

insert into MyTable (id, Field1, Field2, Field3) values ( 1, 'aaa', 'bbb', 'ccc' );
insert into MyTable (id, Field1, Field2, Field3) values ( 2, 'eee', 'fff', 'ggg' );
insert into MyTable (id, Field1, Field2, Field3) values ( 3, 'hhh', 'iii', 'jjj' );

--key/value table
create table MyValuesTable (
    id int,
    [field] sysname,
    [value] nvarchar(10)
);



declare @columnString nvarchar(max)

--This recursive CTE examines the source table's columns excluding
--the 'id' column explicitly and builds a string of column names
--like so: '[Field1], [Field2], [Field3]'.

;with columnNames as (
  select column_id, name
  from sys.columns 
  where object_id = object_id('MyTable','U')
    and name <> 'id'
),
columnString (id, string) as (
  select
    2, cast('' as nvarchar(max))
  union all
  select
    b.id + 1, b.string + case when b.string = '' then '' else ', ' end + '[' + a.name + ']'
  from
    columnNames a
    join columnString b on b.id = a.column_id
)
select top 1 @columnString = string from columnString order by id desc

--Now I build a query around the column names which unpivots the source and inserts into the key/value table.
declare @sql nvarchar(max)
set @sql = '
insert MyValuestable
select id, field, value
from
  (select * from MyTable) b
unpivot
  (value for field in (' + @columnString + ')) as unpvt'

--Query's ready to run.
exec (@sql)

select * from MyValuesTable

如果要从存储过程中获取源数据，则可以使用OPENROWSET将数据获取到表中，然后检查该表的列名.此链接显示了如何执行该部分. https://stackoverflow.com/a/1228165/300242

In case you're getting your source data from a stored procedure, you can use OPENROWSET to get the data into a table, then examine that table's column names. This link shows how to do that part. https://stackoverflow.com/a/1228165/300242

最后的注释:如果使用临时表，请记住，您像这样从tempdb.sys.columns获取列名:

Final note: If you use a temporary table, remember that you get the column names from tempdb.sys.columns like so:

select column_id, name
from tempdb.sys.columns 
where object_id = object_id('tempdb..#MyTable','U')

这篇关于将透视表列取消透视成键值行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！