如何使用CTE结合Group by实现表数据的分组转换？

Core Formatting Examples #

• Emphasize key information with asterisks: This text is highlighted
• Inline code/commands use backticks: INSERT INTO #Temp (NO, Name, Position, [Group]) VALUES (1, 'Raj', 'Manager', '印度')

Bullet Lists #

• Primary list item
• Secondary list item
  • Nested list entry
  • Another nested entry

Block Quotes #

When working with SQL pivots, CTEs combined with GROUP BY often offer more flexibility than the built-in PIVOT operator for custom grouping requirements.

Hyperlinks #

• Reference a SQL concept: Common Table Expressions (CTE)

Image Embedding #

Sample Pivoted Table Output

SQL Implementation Example (Relevant to Your Requirement) #

CTE + GROUP BY for Custom Pivoting #

WITH GroupedRanks AS (
    SELECT
        Name,
        Position,
        [Group],
        -- Assign row number to align names across groups/positions
        ROW_NUMBER() OVER (PARTITION BY Position, [Group] ORDER BY NO) AS RowSeq
    FROM #Temp
)
SELECT
    RowSeq,
    -- Create columns for each Position + Group combination
    MAX(CASE WHEN Position = 'Manager' AND [Group] = '印度' THEN Name END) AS 'Manager_印度',
    MAX(CASE WHEN Position = 'Manager' AND [Group] = '尼泊尔' THEN Name END) AS 'Manager_尼泊尔',
    MAX(CASE WHEN Position = 'Developer' AND [Group] = '印度' THEN Name END) AS 'Developer_印度',
    MAX(CASE WHEN Position = 'Developer' AND [Group] = '尼泊尔' THEN Name END) AS 'Developer_尼泊尔'
    -- Add more CASE statements for additional positions
FROM GroupedRanks
GROUP BY RowSeq
ORDER BY RowSeq;

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