Place multiple related records into single table with extra fields

  • I have these two tables below with a Key field of BOVA which forms a many to one relationship.

    I am trying to join these 2 tables but create additional fields to house the numerous STATUS fields. There can be 1 or up to 72 different STATUS values. This is the basis of the One to Many

    What I am trying to get to is something like this after the JOIN

    There is a mistake in the image below.... 010031 for the BOVA ID should be 010002

    enter image description here

    Can this be done and if so are there any examples around that could show me the way?


    enter image description here


    enter image description here

  • 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
        FROM Table2
    _Table2Pivoted AS -- Pivots the values of the STATUS column into each respective ordered Status# column, for each 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
            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)
    -- Fill in the other Status# columns up to Status72
    FROM _Table2Pivoted AS T2
    LEFT JOIN Table1 AS T1
    ON T2.BOVA = T1.BOVA

    Details are in the comments, but to summarize the steps:

    1. 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.
    2. 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.
    3. 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.
    4. 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: Results

    *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.

Log in to reply

Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2