SQL Gymnastics

Every medical professional has a set of specialties, stored here in an expertise table. This uses a temporary table to define a sequence which is too complex for an ORDER BY clause. Older records are sorted by OrderNo and have SequenceNo=0, and newer records are ordered by SequenceNo. To complicate matters, records are grouped by facility where these specialties are practiced. We group each expertise together and work out the sorting, then build a new expertise table denormalizing these fields so the first five can be placed in a table optimized for fast access. This code was never used.

CREATE VIEW mso_test.ipd2MedProfExpertise AS
WITH ExpertiseList AS (
 SELECT
    row_number() over (
       order by Med_Prof_Record_No, SequenceNo, OrderNo
    ) AS 'RowNumber',
    Expertise,
    Med_Prof_Record_No,
    FacCode
 FROM mso_test.Med_Prof_Expertises
)
SELECT
 e1.Med_Prof_Record_No,
 e1.FacCode,
 e1.Expertise AS Expertise1,
 e2.Expertise AS Expertise2,
 e3.Expertise AS Expertise3,
 e4.Expertise AS Expertise4
FROM ExpertiseList e1
LEFT OUTER JOIN
 ExpertiseList e2 ON (
    e2.Med_Prof_Record_no = e1.Med_Prof_Record_No AND
    e2.FacCode = e1.FacCode AND
    e2.RowNumber > e1.RowNumber
 )
LEFT OUTER JOIN
 ExpertiseList e3 ON (
    e3.Med_Prof_Record_no = e1.Med_Prof_Record_No AND
    e3.FacCode = e1.FacCode AND
    e3.RowNumber > e2.RowNumber
 )
LEFT OUTER JOIN
 ExpertiseList e4 ON (
    e4.Med_Prof_Record_no = e1.Med_Prof_Record_No AND
    e4.FacCode = e1.FacCode AND
    e4.RowNumber > e3.RowNumber
 )
GO