Why does pivoting of a string data column work with row_number?

Question

I had a problem where I need to pivot on a string column. I found the solution in the link below, but cannot understand why pivoting works for column with string data when row_number is used.

Need to Pivot String values in SQL server

Table definition:

ColumnName  DataType
----------  --------
Occupation  String 
Name        String

Data:

Occupation  Name
----------  ----
Developer    A
Developer    B
Designer     X
Coder        Y
Coder        Z

The query used was:

SELECT  [Developer],
        [Designer],
        [Coder]
FROM (
      SELECT  *,
              ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY (SELECT NULL)) 
              RN
      FROM 
           #temp
     ) as t
PIVOT 
    (
      MAX(Name) 
      FOR Occupation 
      IN ([Developer],[Designer],[Coder])
    ) as pvt

Output:

Developer   Designer    Coder
A           X           Y
B           NULL        Z

. . A question should be complete in itself. This needs sample data, desired results, and an appropriate database tag. A link to something that says "I don't understand" is not a valid question. — Gordon Linoff
– Gordon Linoff, Commented May 16, 2020 at 12:22

Vad1m · Accepted Answer · 2020-05-16 09:04:44Z

0

In previous question the 'MAX' aggregation function was used. It returns only one row for table with two columns: one value for developer, one for coder et.c. Row_number function adds a dimension to the table and you get this:

Developer   Designer    Coder
A              X          Y
B             NULL        Z

instead of this:

Developer   Designer    Coder
B              X          Z

answered May 16, 2020 at 9:04

Vad1m

4613 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user9463 Over a year ago

Still can't understand. Can you please elaborate a bit more.

Vad1m Over a year ago

@user9463 pivoting in SQL requires an aggregation function (sum, count, avg, max, min). Since we deal with string data, we can use only MAX and MIN functions. These functions return only one value for a column in pivot table if we have only two columns in input table. So the query returns only letters with maximal ASCII value for each job in this case (B, X and Z). We add a column with row numbers over each job to separate subsets and apply aggregation function to each value instead of their subset. And we get pivot with multiple rows instead of pivot with one row

Collectives™ on Stack Overflow

Why does pivoting of a string data column work with row_number?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related