sql-server - 将结构未知的表转换为键/值

Question

我们从分析师那里收到的报告数据采用任意结构的表格格式。我们只知道每一行都有一CustomerId列。但是其他的，我们不知道，并且每次都会有所不同。

接收此数据的目标系统仅以 Key/Value 格式执行，因此我们必须将报告表转换为 Key/Value。

因此，例如，如果源报表表具有以下结构：

CREATE TABLE [dbo].[SampleSourceTable](
    [CustomerId] [bigint] NULL,
    [Column1] [nchar](10) NULL,
    [Column2] [int] NULL,
    [Column3] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[SampleSourceTable] ([CustomerId], [Column1], [Column2], [Column3]) VALUES (1, N'aaa', 123, CAST(N'2019-01-01T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleSourceTable] ([CustomerId], [Column1], [Column2], [Column3]) VALUES (2, N'bbb', 456, CAST(N'2018-01-01T00:00:00.000' AS DateTime))
GO

我们希望将此数据转换为以下结构：

CREATE TABLE [dbo].[SampleDestinationTable](
    [CustomerId] [bigint] NULL,
    [Attribute] [nvarchar](255) NULL,
    [Value] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[SampleDestinationTable] ([CustomerId], [Attribute], [Value]) VALUES (1, N'Column1', N'aaa')
GO
INSERT [dbo].[SampleDestinationTable] ([CustomerId], [Attribute], [Value]) VALUES (1, N'Column2', N'123')
GO
INSERT [dbo].[SampleDestinationTable] ([CustomerId], [Attribute], [Value]) VALUES (1, N'Column3', N'2019-01-01 00:00:00.000')
GO
INSERT [dbo].[SampleDestinationTable] ([CustomerId], [Attribute], [Value]) VALUES (2, N'Column1', N'bbb')
GO
INSERT [dbo].[SampleDestinationTable] ([CustomerId], [Attribute], [Value]) VALUES (2, N'Column2', N'456')
GO
INSERT [dbo].[SampleDestinationTable] ([CustomerId], [Attribute], [Value]) VALUES (2, N'Column3', N'2018-01-01 00:00:00.000')
GO

然而，这里的挑战是源报告表没有固定的结构。

起初，我考虑使用游标遍历每一行，然后使用嵌套游标遍历该行中的所有列。但显然，没有办法使用 cursors 处理具有未知结构的行。所以现在，我想知道这是否可以使用 PIVOT/UNPIVOT。但话又说回来，我认为他们也需要列列表。

我正在运行 SQL Server 2017。

如何转换具有未知结构的数据？

标签： sql-servertsqlpivot-tablekey-value

Answer 1

一种可能的方法是使用以下信息生成动态语句INFORMATION_SCHEMA.COLUMNS：

-- Declarations
DECLARE @stm nvarchar(max)

-- Dynamic part 
SELECT 
    @stm = STUFF((
        SELECT CONCAT(
            N' UNION ALL SELECT CustomerID, ''', 
            [COLUMN_NAME],
            N''' AS [Attribute], CONVERT(nvarchar(max), ',
            QUOTENAME([COLUMN_NAME]),
            CASE 
                WHEN DATA_TYPE = 'datetime' THEN N', 121'
                -- Add additional conversion rules for other data types
                ELSE N''
            END,
            N') AS [Value]', 
            N' FROM [SampleSourceTable]'
        )
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE (TABLE_NAME = 'SampleSourceTable') AND (COLUMN_NAME <> 'CustomerId')
    FOR XML PATH('')
    ), 1, 11, N'')

-- Whole statement and execution
SET @stm = @stm + N'ORDER BY CustomerID'
PRINT @stm 
EXEC (@stm)

输出：

CustomerID  Attribute   Value
1           Column1     aaa       
1           Column2     123
1           Column3     2019-01-01 00:00:00.000
2           Column3     2018-01-01 00:00:00.000
2           Column2     456
2           Column1     bbb