非规范化数据(也许是一个枢轴?) [英] Denormalizing Data (Maybe A Pivot?)

问题描述

我有一个包含学生编号和相应教师编号的简单表，我需要对其进行非规范化以输入到旧系统.

I have a simple table containing Student Numbers and corresponding Teacher Numbers, and I need to denormalize it for input to a legacy system.

例如，现在的数据如下所示:

For example, here's what the data looks like now:

StudNumber  TeacherNumber
445        57315
445        88584
445        95842
858        88115
858        65443
858        57315
858        21144
858        18657
858        88584
311        64521
665        35512
665        57315
665        15546

我希望它看起来像这样，将每个老师分成一个单独的列，从左到右填充列.一个商业规则是每个学生最多可以有六名老师:

What I'd like it to look like is this, splitting out each Teacher into a separate column, filling columns from left-to-right. One business rule is that each student can have no more than six teachers:

StudNum Teacher1    Teacher2    Teacher3    Teacher4    Teacher5    Teacher6
445     57315       88584        95842
858     88115       65443        57315      21144        18657      88584
311     64521
665     35512       57315        15546

原始表中有 10,000 多行，因此我需要以编程方式执行此操作.谢谢！

There are 10,000+ rows in the original table, so I need to do this programatically. Thank you!

推荐答案

您可以使用pivot.您还需要排名"您的老师 1-6.请参阅我对您要如何执行此操作的评论.目前:

You can use pivot. You also need to "Rank" your teachers 1-6. See my comment on how you want to do this. For now:

Select StudNumber, TeacherNumber, TeacherRank
from (
   Select ST.StudNumber
       , ST.TeacherNumber
       , ROW_NUMBER() OVER (PARTITION BY ST.StudNumber 
                    ORDER BY ST.TeacherNumber) AS TeacherRank
   From StudentTeacher AS ST)
Where TeacherRank <=6

然后您可以根据此声明进行调整.这是一个很好的解释:使用 Pivot 和 UnPivot

Then you can pivot on this statement. Here is a good explanation: Using Pivot and UnPivot

这篇关于非规范化数据(也许是一个枢轴?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！