时间戳

首页 > 解决方案 > SSIS - 将多列拆分为单行

sql-server - SSIS - 将多列拆分为单行

问题描述

下面是我需要拆分的一个非常小的平面表示例。第一个表是具有 ID、名称和持续时间的课程表。第二个表是学生表，只有学生名作为 PK。第三个表将是一个多对多的课程 ID 和学生姓名。

课程编号 课程名称 课时 学生1 学生2 学生3 学生4
1 数学 1小时 让 保罗 简 能源部
2 英语 1小时 让 简 能源部
  1. 我不知道如何使用 SSIS，使用 Student 1、2、3 和 4 列将 Jean、Paul、Jane 和 Doe 分配到他们自己的表中。当我弄清楚这一点时，我想我可以使用相同的逻辑将课程 ID 和列映射到第三个多对多表？
  2. 如何处理重复条目，例如 Jean Jane 和 Doe 已经从第一行存在，因此不需要将它们添加到学生表中。
  3. 我假设我使用条件拆分来跳过空值？例如，第二行的 Student4 为 Null。

感谢您的帮助。

标签： sql-serverdatabasevisual-studiossisrelational-database

解决方案

如果是我，我会将其设计为 3 个数据流。

数据流 1 - 学生人数

由于我们假设名称是使学生独一无二的原因，因此我们需要建立一个唯一名称的大列表。

SELECT D.*
FROM
(
    SELECT S.Student1 AS StudentName
    FROM dbo.MyTable AS S
    UNION
    SELECT S.Student2 AS StudentName
    FROM dbo.MyTable AS S
    UNION
    SELECT S.Student3 AS StudentName
    FROM dbo.MyTable AS S
    UNION
    SELECT S.Student4 AS StudentName
    FROM dbo.MyTable AS S
)D
WHERE D.StudentName IS NOT NULL
ORDER BY D.StudentName;

查询中的使用UNION将处理数据的重复数据删除，我们将其包装在派生表中以过滤 NULL。

我添加了一个明确的顺序，但因为我假设您使用名称作为主键，所以我们在获取数据时避免排序操作。

将 OLE DB 源添加到您的数据流中，而不是在下拉列表中选择表，您将使用上述查询。

将 OLE DB 目标添加到同一数据流并连接两者。假设您的目标表看起来像

CREATE TABLE dbo.Student
(
    StudentName varchar(50) NOT NULL CONSTRAINT PK__dbo__Student PRIMARY KEY(StudentName)
);

数据流 2 - 课程

经销商选择在这里，您可以编写查询或仅指向源表。

使用 SSIS 的一个非常好的做法是只将您需要的数据带入缓冲区，所以我会编写一个类似的查询

SELECT DISTINCT S.[Lesson Id], S.[Lesson Name], S.[Lesson Duration]
FROM dbo.MyTable AS S;

我喜欢这里的独特之处，因为我对您的数据知之甚少，但如果它被扩展并且提供第二堂数学课以容纳另外 4 名学生，它可能又是第 1 课。或者它可能是 3，因为它表示课程时间或其他内容。

添加一个 OLE DB 目标并登陆数据。

数据流 3 - 多对多

有几种不同的方法可以处理这个问题。我倾向于懒惰的方式，并从第一个数据流中重复我们的方法

SELECT D.*
FROM
(
    SELECT S.Student1 AS StudentName, S.[Lesson Id]
    FROM dbo.MyTable AS S
    UNION
    SELECT S.Student2 AS StudentName, S.[Lesson Id]
    FROM dbo.MyTable AS S
    UNION
    SELECT S.Student3 AS StudentName, S.[Lesson Id]
    FROM dbo.MyTable AS S
    UNION
    SELECT S.Student4 AS StudentName, S.[Lesson Id]
    FROM dbo.MyTable AS S
)D
WHERE D.StudentName IS NOT NULL
ORDER BY D.StudentName;

然后使用 OLE DB 目标登陆您的桥接表并完成它。

如果这是让您学习本机组件的家庭作业/任务...

坚持使用 3 数据流方法。一次尝试做太多事情会带来麻烦。

将宽数据移动到窄数据的操作是Unpivot操作。你会在学生和桥表数据流中使用它，但老实说，我认为我在我的职业生涯中使用该组件的次数不到 10 次和/或在这里回答 SSIS 问题，我做了很多。

如果 Unpivot 操作生成 NULL，那么是的，您可能希望使用条件拆分来过滤掉这些行。

如果您的参考表更复杂，那么您可能会在桥接表填充步骤中添加一个查找组件来检索代理键。

推荐阅读