SQL Server视图实现子表值转父表列：多子表多值场景

No problem at all—even without relying on the PIVOT operator, we can use window functions + conditional aggregation to turn those top 5 child records into columns. Here's a practical, step-by-step solution tailored to your table structure:

Step 1: Add Row Numbers to Child Records #

First, we'll use ROW_NUMBER() to tag each child record with its position relative to its parent. This gives us the "sequence identifier" we need to pivot later, since your child tables don't have built-in ordering fields.

WITH RankedChild1 AS (
    SELECT 
        ParentID,
        Barcode,
        -- Assign row number per parent; adjust ORDER BY to prioritize records (e.g., by Barcode or a date if available)
        ROW_NUMBER() OVER (PARTITION BY ParentID ORDER BY Barcode) AS RowNum
    FROM Child1
),
RankedChild2 AS (
    SELECT 
        ParentID,
        [Group],
        ROW_NUMBER() OVER (PARTITION BY ParentID ORDER BY [Group]) AS RowNum
    FROM Child2
)

Step 2: Join and Pivot with Conditional Aggregation #

Next, we'll join these ranked child CTEs to the Master table, then use MAX(CASE...) to pivot the top 5 rows into dedicated columns. This ensures each parent row only appears once, with all relevant child values in line.

SELECT
    m.ID,
    m.Description,
    -- Pivot top 5 Barcodes into columns
    MAX(CASE WHEN rc1.RowNum = 1 THEN rc1.Barcode END) AS Barcode1,
    MAX(CASE WHEN rc1.RowNum = 2 THEN rc1.Barcode END) AS Barcode2,
    MAX(CASE WHEN rc1.RowNum = 3 THEN rc1.Barcode END) AS Barcode3,
    MAX(CASE WHEN rc1.RowNum = 4 THEN rc1.Barcode END) AS Barcode4,
    MAX(CASE WHEN rc1.RowNum = 5 THEN rc1.Barcode END) AS Barcode5,
    -- Pivot top 5 Groups into columns
    MAX(CASE WHEN rc2.RowNum = 1 THEN rc2.[Group] END) AS Group1,
    MAX(CASE WHEN rc2.RowNum = 2 THEN rc2.[Group] END) AS Group2,
    MAX(CASE WHEN rc2.RowNum = 3 THEN rc2.[Group] END) AS Group3,
    MAX(CASE WHEN rc2.RowNum = 4 THEN rc2.[Group] END) AS Group4,
    MAX(CASE WHEN rc2.RowNum = 5 THEN rc2.[Group] END) AS Group5
FROM Master m
LEFT JOIN RankedChild1 rc1 ON m.ID = rc1.ParentID
LEFT JOIN RankedChild2 rc2 ON m.ID = rc2.ParentID
GROUP BY m.ID, m.Description;

Step 3: Wrap It into a Reusable View #

To make this query accessible anytime, just wrap the entire logic in a CREATE VIEW statement:

CREATE VIEW ParentWithTopChildren AS
WITH RankedChild1 AS (
    SELECT 
        ParentID,
        Barcode,
        ROW_NUMBER() OVER (PARTITION BY ParentID ORDER BY Barcode) AS RowNum
    FROM Child1
),
RankedChild2 AS (
    SELECT 
        ParentID,
        [Group],
        ROW_NUMBER() OVER (PARTITION BY ParentID ORDER BY [Group]) AS RowNum
    FROM Child2
)
SELECT
    m.ID,
    m.Description,
    MAX(CASE WHEN rc1.RowNum = 1 THEN rc1.Barcode END) AS Barcode1,
    MAX(CASE WHEN rc1.RowNum = 2 THEN rc1.Barcode END) AS Barcode2,
    MAX(CASE WHEN rc1.RowNum = 3 THEN rc1.Barcode END) AS Barcode3,
    MAX(CASE WHEN rc1.RowNum = 4 THEN rc1.Barcode END) AS Barcode4,
    MAX(CASE WHEN rc1.RowNum = 5 THEN rc1.Barcode END) AS Barcode5,
    MAX(CASE WHEN rc2.RowNum = 1 THEN rc2.[Group] END) AS Group1,
    MAX(CASE WHEN rc2.RowNum = 2 THEN rc2.[Group] END) AS Group2,
    MAX(CASE WHEN rc2.RowNum = 3 THEN rc2.[Group] END) AS Group3,
    MAX(CASE WHEN rc2.RowNum = 4 THEN rc2.[Group] END) AS Group4,
    MAX(CASE WHEN rc2.RowNum = 5 THEN rc2.[Group] END) AS Group5
FROM Master m
LEFT JOIN RankedChild1 rc1 ON m.ID = rc1.ParentID
LEFT JOIN RankedChild2 rc2 ON m.ID = rc2.ParentID
GROUP BY m.ID, m.Description;

Quick Notes: #

• Tweak the ORDER BY in the ROW_NUMBER() clauses to control which child records get picked first (e.g., use a creation date instead of Barcode/Group for chronological order).
• If a parent has fewer than 5 child records, extra columns (like Barcode4/5) will return NULL—wrap the MAX() in ISNULL() if you want to replace these with empty strings or a custom default value.

内容的提问来源于stack exchange，提问作者Robbie Vettenburg