SQL: повторяющиеся экземпляры в одну строку

Question

Я запускаю базовый SQL-запрос в нашей базе данных, который возвращает данные об учениках. Студент может иметь несколько экземпляров на разных курсах, например:

StudentID        StudentFullName        Course        StartDate
123456           J.Bloggs               BA            01/11/2012
123456           J.Bloggs               MBA           01/07/2015
234567           R.Head                 BA            09/04/2014

То, что я пытаюсь сделать, - это объединить дубликат записи ученика (возможно, на основе идентификатора или имени) и поместить данные курса в одну строку, в результате чего конечный результат в идеале будет выглядеть следующим образом:

StudentID        StudentFullName        Course1       StartDate1       Course2    StartDate2
123456           J.Bloggs               BA            01/11/2012       MBA        01/07/2015
234567           R.Head                 BA            09/04/2014

Могут быть случаи, когда у студентов более двух курсов, поэтому код должен быть гибким, чтобы это разрешить (курс 3, курс 4 и т. Д.).

Я также хотел бы выполнить запрос, чтобы найти студентов, начавших курс "Курс1" через определенный промежуток времени (>= "2014/08/01").

До сих пор мне удавалось воссоздать желаемый результат вручную, запустив исходный код и затем отредактировав его в электронной таблице (это отнимает много времени).

Answer 1

Тем более, что количество ваших курсов является гибким, вы не сможете поместить это в одну таблицу. Таким образом, SQL Server не поддерживает "гибкие таблицы столбцов" как концепцию.

Вместо этого ваша проблема может быть одной из нормализации к форме 2NF.

Рассмотрите возможность создания новой таблицы StudentCourses со столбцами Student ID, Course, Start Date. У такого студента, как J.Bloggs, будет две записи, а у R.Head - только одна. Ваша текущая таблица учеников остается только с информацией ученика, такой как имя, дата рождения, кредиты и т. Д.

Затем, в зависимости от ваших потребностей, вы всегда можете создавать запросы или представления, которые собирают эту информацию.

Answer 2

Используете ли вы SQL в любом другом языке программирования? Если это так, вы можете использовать if-Statement для добавления новых столбцов, но динамическое добавление или удаление столбцов может вызвать аномалии. Я думаю, что создание отдельной таблицы даст вам лучшие результаты. Тогда у вас должно быть три таблицы:

Данныестудента (StudentID, StudentFullName)

Курсы(CourseID, CourseName)

Студенческие курсы(CourseID,StudentID, StartDate)

Это оптимизированная схема.

Надеюсь, я мог бы помочь, если нет, пожалуйста, не стесняйтесь задавать дополнительные вопросы.

РЕДАКТИРОВАТЬ: Возможно, потребуется больше усилий, потому что курсы хранятся отдельно, но это должно быть самым быстрым и наиболее экономичным решением для хранения. как уже упоминалось в комментарии: это нормализация схемы.

Возможно, проще было бы схема с двумя таблицами: StudentData(StudentID, StudentFullName) StudentCourses(CourseName,StudentID, StartDate)

Answer 3

Одним из самых простых способов поворота нескольких столбцов является использование обобщенных выражений регистра.

Вы должны использовать ROW_NUMBER, чтобы определить, является ли курс course1, course2 и т. Д., А затем просто использовать сгенерированный ROW_NUMBER в вашем выражении дела.

SELECT  [StudentID],
        [StudentFullName],
        MAX(CASE WHEN Rn = 1 THEN [Course] END) AS Course1,
        MAX(CASE WHEN Rn = 1 THEN [StartDate] END) AS StartDate1,
        MAX(CASE WHEN Rn = 2 THEN [Course] END) AS Course2,
        MAX(CASE WHEN Rn = 2 THEN [StartDate] END) AS StartDate2
FROM    (
            SELECT  *, ROW_NUMBER() OVER (PARTITION BY [StudentID] ORDER BY [StartDate] DESC) Rn
            FROM    Table1
        ) t
GROUP BY [StudentID],
        [StudentFullName]

Эти запросы также можно выполнять динамически, просто создавая часть MAX(CASE).