为何添加Row_Number()能让SQL Pivot查询返回多行结果？

Let's break down exactly what's happening here, step by step:

The Problem with Your First Query #

When you use SQL's PIVOT operator, it automatically groups your results by all columns that aren't part of the pivot logic (i.e., not the column being pivoted on, and not the column being aggregated).

In your initial subquery:

Select Occupation, Name From Occupations

You only have two columns: Occupation (the pivot column) and Name (the aggregated column). There are no other columns to group by, so SQL treats the entire dataset as a single group.

When you run Pivot (Max(Name) For Occupation in ([Actor],[Singer])), it just picks one value per occupation (in your case, Sam for Actor and Julia for Singer) and returns that as a single row—since there's no grouping key to split it into multiple rows.

How Row_Number() Solves It #

Adding the Row_Number() function changes everything because it introduces a grouping key that PIVOT can use to split results into multiple rows:

Select Occupation, Name, Row_Number() over(partition by Occupation order by Name) SNo From Occupations

Here's what this does:

• partition by Occupation splits the data into separate groups for each job (Actor, Singer).
• order by Name sorts the names within each group.
• Row_Number() assigns a sequential number (SNo) to each name in its occupation group. So Sam gets 1, Ketty gets 2 (for Actor), and Julia gets 1 (for Singer).

Now, when you run the pivot, SQL uses SNo as the grouping column. For each unique SNo value, it creates a row:

• For SNo = 1: It pulls the first name from Actor (Sam) and the first name from Singer (Julia).
• For SNo = 2: It pulls the second name from Actor (Ketty), but since there's no second name in Singer, it fills that spot with null.

This gives you the two-row result you were expecting.

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