Pivoting and partitioning Query to get Names

I tried the HackerRank question below where the Names have to be pivoted on the Occupations.

https://www.hackerrank.com/challenges/occupations/problem

I found a solution, but don't understand how it works specifically why are the other names returning in the result when taking a max on the names.

Table:

CREATE TABLE Occupations

(

    Name varchar(25),

    Occupation varchar(1000)

)



INSERT INTO Occupations VALUES('Samantha', 'Doctor')

GO

INSERT INTO Occupations VALUES('Julia', 'Actor')

GO

INSERT INTO Occupations VALUES('Maria', 'Actor')

GO

INSERT INTO Occupations VALUES('Meera', 'Singer')

GO

INSERT INTO Occupations VALUES('Ashley', 'Professor')

GO

INSERT INTO Occupations VALUES('Ketty', 'Professor')

GO

INSERT INTO Occupations VALUES('Christeen', 'Singer')

GO

INSERT INTO Occupations VALUES('Jane', 'Actor')

GO

INSERT INTO Occupations VALUES('Jenny', 'Doctor')

GO

INSERT INTO Occupations VALUES('Priya', 'Singer')

GO

Solution:

SELECT [Doctor], [Professor], [Singer],[Actor] 

FROM 

(   

    SELECT ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name ASC) AS RowNumber, * 

    FROM Occupations) as tempTable 

    PIVOT 

    (

        MAX(Name) FOR OCCUPATION IN ([Doctor], [Professor], [Singer], [Actor])

    ) AS pivotTable

So the way I understand this query gets executed is that the rows get partitioned by Occupation ordered by the name and then the max names gets pivoted on the Occupations. So how does the query still return the other names by occupation?

asked Nov 22 '18 at 1:16

rds80

1279

1

The way PIVOT works is it groups on the rest of the columns that were not in the PIVOT. Here you've assigned a row_number() partitioned by occupation, so each name in an occupation is assigned a number (1, 2, 3, etc...). The pivot will find the max(name) for each occupation with rownumber = 1, max(name) for each occupation with rownumber = 2, etc... Essentially, the row_number() window function is splitting the names into distinct groups that all get returned as a result.

– ZLK
Nov 22 '18 at 3:36

2

It might help to think of it in terms of writing it without PIVOT. For example, SELECT [Doctor] = MAX(CASE WHEN Occupation = 'Doctor' THEN Name END), [Professor] = MAX(CASE WHEN Occupation = 'Professor' THEN Name END), [Singer] = MAX(CASE WHEN Occupation = 'Singer' THEN Name END), [Actor] = MAX(CASE WHEN Occupation = 'Actor' THEN Name END) FROM (SELECT *, RowNumber = ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) FROM Occupations) AS O GROUP BY RowNumber;

– ZLK
Nov 22 '18 at 3:42

That's a good explanation. I didn't realize PIVOT groups on the columns that aren't included in the PIVOT.

– rds80
Nov 22 '18 at 13:17

Does the pivot query and the group by query that you wrote work the same way?

– rds80
Nov 22 '18 at 13:51

Pretty much, yes. You'll probably see similar execution plans for both if you run both of them for a query as simple as this one. But the principle behind them is the same regardless. If you look for examples of how to pivot in SQL Server on SO, you'll find many similar examples using CASE and aggregates.

– ZLK
Nov 22 '18 at 21:12

add a comment |

I tried the HackerRank question below where the Names have to be pivoted on the Occupations.

https://www.hackerrank.com/challenges/occupations/problem

I found a solution, but don't understand how it works specifically why are the other names returning in the result when taking a max on the names.

Table:

CREATE TABLE Occupations

(

    Name varchar(25),

    Occupation varchar(1000)

)



INSERT INTO Occupations VALUES('Samantha', 'Doctor')

GO

INSERT INTO Occupations VALUES('Julia', 'Actor')

GO

INSERT INTO Occupations VALUES('Maria', 'Actor')

GO

INSERT INTO Occupations VALUES('Meera', 'Singer')

GO

INSERT INTO Occupations VALUES('Ashley', 'Professor')

GO

INSERT INTO Occupations VALUES('Ketty', 'Professor')

GO

INSERT INTO Occupations VALUES('Christeen', 'Singer')

GO

INSERT INTO Occupations VALUES('Jane', 'Actor')

GO

INSERT INTO Occupations VALUES('Jenny', 'Doctor')

GO

INSERT INTO Occupations VALUES('Priya', 'Singer')

GO

Solution:

SELECT [Doctor], [Professor], [Singer],[Actor] 

FROM 

(   

    SELECT ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name ASC) AS RowNumber, * 

    FROM Occupations) as tempTable 

    PIVOT 

    (

        MAX(Name) FOR OCCUPATION IN ([Doctor], [Professor], [Singer], [Actor])

    ) AS pivotTable

asked Nov 22 '18 at 1:16

rds80

1279

1

The way PIVOT works is it groups on the rest of the columns that were not in the PIVOT. Here you've assigned a row_number() partitioned by occupation, so each name in an occupation is assigned a number (1, 2, 3, etc...). The pivot will find the max(name) for each occupation with rownumber = 1, max(name) for each occupation with rownumber = 2, etc... Essentially, the row_number() window function is splitting the names into distinct groups that all get returned as a result.

– ZLK
Nov 22 '18 at 3:36

2

It might help to think of it in terms of writing it without PIVOT. For example, SELECT [Doctor] = MAX(CASE WHEN Occupation = 'Doctor' THEN Name END), [Professor] = MAX(CASE WHEN Occupation = 'Professor' THEN Name END), [Singer] = MAX(CASE WHEN Occupation = 'Singer' THEN Name END), [Actor] = MAX(CASE WHEN Occupation = 'Actor' THEN Name END) FROM (SELECT *, RowNumber = ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) FROM Occupations) AS O GROUP BY RowNumber;

– ZLK
Nov 22 '18 at 3:42

That's a good explanation. I didn't realize PIVOT groups on the columns that aren't included in the PIVOT.

– rds80
Nov 22 '18 at 13:17

Does the pivot query and the group by query that you wrote work the same way?

– rds80
Nov 22 '18 at 13:51

Pretty much, yes. You'll probably see similar execution plans for both if you run both of them for a query as simple as this one. But the principle behind them is the same regardless. If you look for examples of how to pivot in SQL Server on SO, you'll find many similar examples using CASE and aggregates.

– ZLK
Nov 22 '18 at 21:12

add a comment |

I tried the HackerRank question below where the Names have to be pivoted on the Occupations.

https://www.hackerrank.com/challenges/occupations/problem

I found a solution, but don't understand how it works specifically why are the other names returning in the result when taking a max on the names.

Table:

CREATE TABLE Occupations

(

    Name varchar(25),

    Occupation varchar(1000)

)



INSERT INTO Occupations VALUES('Samantha', 'Doctor')

GO

INSERT INTO Occupations VALUES('Julia', 'Actor')

GO

INSERT INTO Occupations VALUES('Maria', 'Actor')

GO

INSERT INTO Occupations VALUES('Meera', 'Singer')

GO

INSERT INTO Occupations VALUES('Ashley', 'Professor')

GO

INSERT INTO Occupations VALUES('Ketty', 'Professor')

GO

INSERT INTO Occupations VALUES('Christeen', 'Singer')

GO

INSERT INTO Occupations VALUES('Jane', 'Actor')

GO

INSERT INTO Occupations VALUES('Jenny', 'Doctor')

GO

INSERT INTO Occupations VALUES('Priya', 'Singer')

GO

Solution:

SELECT [Doctor], [Professor], [Singer],[Actor] 

FROM 

(   

    SELECT ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name ASC) AS RowNumber, * 

    FROM Occupations) as tempTable 

    PIVOT 

    (

        MAX(Name) FOR OCCUPATION IN ([Doctor], [Professor], [Singer], [Actor])

    ) AS pivotTable

asked Nov 22 '18 at 1:16

rds80

1279

I tried the HackerRank question below where the Names have to be pivoted on the Occupations.

https://www.hackerrank.com/challenges/occupations/problem

I found a solution, but don't understand how it works specifically why are the other names returning in the result when taking a max on the names.

Table:

CREATE TABLE Occupations

(

    Name varchar(25),

    Occupation varchar(1000)

)



INSERT INTO Occupations VALUES('Samantha', 'Doctor')

GO

INSERT INTO Occupations VALUES('Julia', 'Actor')

GO

INSERT INTO Occupations VALUES('Maria', 'Actor')

GO

INSERT INTO Occupations VALUES('Meera', 'Singer')

GO

INSERT INTO Occupations VALUES('Ashley', 'Professor')

GO

INSERT INTO Occupations VALUES('Ketty', 'Professor')

GO

INSERT INTO Occupations VALUES('Christeen', 'Singer')

GO

INSERT INTO Occupations VALUES('Jane', 'Actor')

GO

INSERT INTO Occupations VALUES('Jenny', 'Doctor')

GO

INSERT INTO Occupations VALUES('Priya', 'Singer')

GO

Solution:

SELECT [Doctor], [Professor], [Singer],[Actor] 

FROM 

(   

    SELECT ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name ASC) AS RowNumber, * 

    FROM Occupations) as tempTable 

    PIVOT 

    (

        MAX(Name) FOR OCCUPATION IN ([Doctor], [Professor], [Singer], [Actor])

    ) AS pivotTable

sql sql-server sql-server-2008-r2 row-number

asked Nov 22 '18 at 1:16

rds80

1279

asked Nov 22 '18 at 1:16

rds80

1279

asked Nov 22 '18 at 1:16

rds80

1279

asked Nov 22 '18 at 1:16

rds80

1279

asked Nov 22 '18 at 1:16

rds80

1279

1

The way PIVOT works is it groups on the rest of the columns that were not in the PIVOT. Here you've assigned a row_number() partitioned by occupation, so each name in an occupation is assigned a number (1, 2, 3, etc...). The pivot will find the max(name) for each occupation with rownumber = 1, max(name) for each occupation with rownumber = 2, etc... Essentially, the row_number() window function is splitting the names into distinct groups that all get returned as a result.

– ZLK
Nov 22 '18 at 3:36

2

It might help to think of it in terms of writing it without PIVOT. For example, SELECT [Doctor] = MAX(CASE WHEN Occupation = 'Doctor' THEN Name END), [Professor] = MAX(CASE WHEN Occupation = 'Professor' THEN Name END), [Singer] = MAX(CASE WHEN Occupation = 'Singer' THEN Name END), [Actor] = MAX(CASE WHEN Occupation = 'Actor' THEN Name END) FROM (SELECT *, RowNumber = ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) FROM Occupations) AS O GROUP BY RowNumber;

– ZLK
Nov 22 '18 at 3:42

That's a good explanation. I didn't realize PIVOT groups on the columns that aren't included in the PIVOT.

– rds80
Nov 22 '18 at 13:17

Does the pivot query and the group by query that you wrote work the same way?

– rds80
Nov 22 '18 at 13:51

Pretty much, yes. You'll probably see similar execution plans for both if you run both of them for a query as simple as this one. But the principle behind them is the same regardless. If you look for examples of how to pivot in SQL Server on SO, you'll find many similar examples using CASE and aggregates.

– ZLK
Nov 22 '18 at 21:12

add a comment |

1

The way PIVOT works is it groups on the rest of the columns that were not in the PIVOT. Here you've assigned a row_number() partitioned by occupation, so each name in an occupation is assigned a number (1, 2, 3, etc...). The pivot will find the max(name) for each occupation with rownumber = 1, max(name) for each occupation with rownumber = 2, etc... Essentially, the row_number() window function is splitting the names into distinct groups that all get returned as a result.

– ZLK
Nov 22 '18 at 3:36

2

It might help to think of it in terms of writing it without PIVOT. For example, SELECT [Doctor] = MAX(CASE WHEN Occupation = 'Doctor' THEN Name END), [Professor] = MAX(CASE WHEN Occupation = 'Professor' THEN Name END), [Singer] = MAX(CASE WHEN Occupation = 'Singer' THEN Name END), [Actor] = MAX(CASE WHEN Occupation = 'Actor' THEN Name END) FROM (SELECT *, RowNumber = ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) FROM Occupations) AS O GROUP BY RowNumber;

– ZLK
Nov 22 '18 at 3:42

That's a good explanation. I didn't realize PIVOT groups on the columns that aren't included in the PIVOT.

– rds80
Nov 22 '18 at 13:17

Does the pivot query and the group by query that you wrote work the same way?

– rds80
Nov 22 '18 at 13:51

Pretty much, yes. You'll probably see similar execution plans for both if you run both of them for a query as simple as this one. But the principle behind them is the same regardless. If you look for examples of how to pivot in SQL Server on SO, you'll find many similar examples using CASE and aggregates.

– ZLK
Nov 22 '18 at 21:12

The way PIVOT works is it groups on the rest of the columns that were not in the PIVOT. Here you've assigned a row_number() partitioned by occupation, so each name in an occupation is assigned a number (1, 2, 3, etc...). The pivot will find the max(name) for each occupation with rownumber = 1, max(name) for each occupation with rownumber = 2, etc... Essentially, the row_number() window function is splitting the names into distinct groups that all get returned as a result.

– ZLK
Nov 22 '18 at 3:36

It might help to think of it in terms of writing it without PIVOT. For example,

SELECT [Doctor] = MAX(CASE WHEN Occupation = 'Doctor' THEN Name END), [Professor] = MAX(CASE WHEN Occupation = 'Professor' THEN Name END), [Singer] = MAX(CASE WHEN Occupation = 'Singer' THEN Name END), [Actor] = MAX(CASE WHEN Occupation = 'Actor' THEN Name END) FROM (SELECT *, RowNumber = ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) FROM Occupations) AS O GROUP BY RowNumber;

– ZLK
Nov 22 '18 at 3:42

It might help to think of it in terms of writing it without PIVOT. For example,

SELECT [Doctor] = MAX(CASE WHEN Occupation = 'Doctor' THEN Name END), [Professor] = MAX(CASE WHEN Occupation = 'Professor' THEN Name END), [Singer] = MAX(CASE WHEN Occupation = 'Singer' THEN Name END), [Actor] = MAX(CASE WHEN Occupation = 'Actor' THEN Name END) FROM (SELECT *, RowNumber = ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) FROM Occupations) AS O GROUP BY RowNumber;

– ZLK
Nov 22 '18 at 3:42

That's a good explanation. I didn't realize PIVOT groups on the columns that aren't included in the PIVOT.

– rds80
Nov 22 '18 at 13:17

Does the pivot query and the group by query that you wrote work the same way?

– rds80
Nov 22 '18 at 13:51

Pretty much, yes. You'll probably see similar execution plans for both if you run both of them for a query as simple as this one. But the principle behind them is the same regardless. If you look for examples of how to pivot in SQL Server on SO, you'll find many similar examples using CASE and aggregates.

– ZLK
Nov 22 '18 at 21:12

add a comment |

0

active

oldest

votes

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53422629%2fpivoting-and-partitioning-query-to-get-names%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

0

active

oldest

votes

0

active

oldest

votes

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu