So I think this will accomplish what you need, with a few manipulations of your tables via CTEs:
WITH _Table2Sorted AS -- Sorts the STATUS column by value ascending with SortId "buckets" to later be transposed on with a pivot
(
SELECT
BOVA,
[STATUS],
ROW_NUMBER() OVER (PARTITION BY BOVA ORDER BY [STATUS]) AS SortId
FROM Table2
),
_Table2Pivoted AS -- Pivots the values of the STATUS column into each respective ordered Status# column, for each BOVA
(
SELECT
PT.BOVA,
MAX(PT.[1]) AS Status1, -- Aggregate functions needed on these fields because of the GROUP BY clause, but doesn't change the values here because every other row has NULL
MAX(PT.[2]) AS Status2,
MAX(PT.[3]) AS Status3,
MAX(PT.[4]) AS Status4,
MAX(PT.[5]) AS Status5,
MAX(PT.[6]) AS Status6,
MAX(PT.[7]) AS Status7
-- You'll need to fill in the remaining columns for each status, up to Status72
FROM _Table2Sorted
PIVOT
(
MAX([STATUS]) FOR SortId IN ([1], [2], [3], [4], [5], [6], [7]) -- You'll need to fill in the remaining columns for each status, up to [72]
) AS PT
GROUP BY PT.BOVA -- Grouping needed to collapse all the pivoted rows into a single row per BOVA
)
-- Final results joined back to Table1 to get additional meta-data (PHYLUM, CLASS, etc)
SELECT
T2.BOVA,
T2.[Status1],
T2.[Status2],
T2.[Status3],
T2.[Status4],
T2.[Status5],
T2.[Status6],
T2.[Status7],
-- Fill in the other Status# columns up to Status72
T1.PHYLUM,
T1.CLASS
FROM _Table2Pivoted AS T2
LEFT JOIN Table1 AS T1
ON T2.BOVA = T1.BOVA
Details are in the comments, but to summarize the steps:
- First this uses a window function (
ROW_NUMBER()) to generate a list of SortIds to be used as buckets when we pivot in the next step.
- Then pivot the data into columns for each
SortId we generated in the previous step, putting the value of STATUS into each respective SortId bucket.
- The output of the
PIVOT results in multiple rows per BOVA (1 for each STATUS value, and NULL in each other column) so we must use GROUP BY to collapse the rows into a single row per BOVA.
- Finally select the results of the pivoted CTE and join them to
Table1 to get the additional meta-data needed such as PHYLUM and CLASS.
There might be a more efficient way to accomplish your goal, but this seems pretty decent.
Example output with a subset of sample data you provided:
*Note my comments in the code where you'll need to fill in the other Status# columns I left out (for simplicity) up to Status72.