时间戳

首页 > 解决方案 > 根据公共列值将定义的行数转换为列

sql - 根据公共列值将定义的行数转换为列

问题描述

我在 SQL Server 的表中有以下数据集，如下所示：

Table Name: Metadata

IdMetadata  IdMetadataKey   Value                   IdBatch
1           3               50550000                105
2           2               ABC                     105
3           5               Buks                    105
4           1               Bruwer                  105
5           4               AB1234567/4             105
6           6               Annuity Tax Directive   105
7           3               50594644                105
8           2               ABC                     105
9           5               Jami                    105
10          1               Leigh                   105
11          4               9401213056080           105
12          6               Redemption              105
13          3               50550026                106
14          2               ABC                     106
15          5               John                    106
16          1               Smith                   106
17          4               6812305023089           106
18          6               Tax Free Transfer Out   106

IdMetadataKey 中的列值是指向以下主表的外键

Table Name: MetadataKey

IdMetadataKey   Description
1               LAST_NAME
2               COMPANY
3               INVESTOR_ID
4               IDENTIFICATION_NUMBER
5               FIRST_NAME
6               WORK_DESCRIPTION

我想使用上面提到的第一个表来完成以下内容：

Investor_Id First_Name  Last_Name   ID_Number    Work_Description       Company IdBatch
50550000    Buks        Bruwer      AB1234567/4  Annuity Tax Directive  ABC     105
50594644    Jami        Leigh       555254       Redemption             ABC     105
50550026    John        Smith       9958586      Tax Free Transfer Out  ABC     106

对上述结果的解释是这样的：

  1. 以 IdBatch 105 为例，查找该批次的所有投资者及其相关信息。因此批次 105 的 2 条记录

  2. IdBatch 106 也是如此。但这里只找到一条记录（投资者）

我曾尝试Pivot在 SQL 中使用该命令来实现该结果，但无法使其正常工作。任何人都可以提出一种获得预期结果的方法。

旁注：表元数据中的批次总是有 6 (* n) 行。

标签： sqlsql-serverpivot

解决方案

一种方法是使用条件聚合将行“透视”到列。您知道您始终拥有相同的列，因此您可以简单地使用case来选择每列的值。

请注意，您的示例输出 (ID_Number) 与您的示例数据不完全匹配，假设这只是示例数据准备监督。

您还需要一个额外的列来将 Investor_Id 组合在一起。

select
  max(case idMetadataKey when 3 then value end) Investor_Id,
  max(case idMetadataKey when 5 then value end) First_Name,
  max(case idMetadataKey when 1 then value end) Last_Name,
  max(case idMetadataKey when 4 then value end) Id_Number,
  max(case idMetadataKey when 6 then value end) Work_Description,
  max(case idMetadataKey when 2 then value end) Company,
 IDBatch
from (
    select *, Row_Number() over(partition by idbatch, IdMetadataKey order by IdMetadata) grp
    from Metadatad 
)m
group by IDBatch, grp
order by IDBatch, grp

看到这个DBFiddle

推荐阅读