azure-data-factory - 如何使用 Azure 数据流将 2 个相同的行合并为只有一列差异的单行

Question

如何使用 Azure 数据流将以下 2 条记录合并为单行

帐号	电话类型	家庭号码	手机号码
1234	家	989 324 1234
1234	移动的		9893238893

所需输出

帐号	家庭号码	手机号码
1234	989 324 1234	989 323 8893

标签： azure-data-factoryazure-data-flow

Answer 1

假设NULL值将保存在空列中，您可以简单地max在活动中使用函数aggregate来实现预期的行为。

我使用的测试用例，一个内联数据集：

SELECT 1234 AS accountNumber, 'Home' AS phoneType, '989 324 1234' AS homeNumber, null AS mobileNumber
UNION ALL
SELECT 1234 AS accountNumber, 'Mobile' AS phoneType, null AS homeNumber, '9893238893' AS mobileNumber

聚合阶段：

数据预览结果：

数据流的完整 json：

{
    "name": "dataflow1",
    "properties": {
        "type": "MappingDataFlow",
        "typeProperties": {
            "sources": [
                {
                    "linkedService": {
                        "referenceName": "azuresqluat1",
                        "type": "LinkedServiceReference"
                    },
                    "name": "source1"
                }
            ],
            "sinks": [],
            "transformations": [
                {
                    "name": "Aggregate1"
                }
            ],
            "script": "source(output(\n\t\taccountNumber as integer,\n\t\tphoneType as string,\n\t\thomeNumber as string,\n\t\tmobileNumber as string\n\t),\n\tallowSchemaDrift: true,\n\tvalidateSchema: false,\n\tformat: 'query',\n\tstore: 'sqlserver',\n\tquery: 'SELECT 1234 AS accountNumber, \\'Home\\' AS phoneType, \\'989 324 1234\\' AS homeNumber, \\'\\' AS mobileNumber\\nUNION ALL\\nSELECT 1234 AS accountNumber, \\'Mobile\\' AS phoneType, \\'\\' AS homeNumber, \\'9893238893\\' AS mobileNumber',\n\tisolationLevel: 'READ_UNCOMMITTED') ~> source1\nsource1 aggregate(groupBy(accountNumber),\n\thomeNumber = max(homeNumber),\n\t\tmobileNumber = max(mobileNumber)) ~> Aggregate1"
        }
    }
}