Sql hackerrank occupations. Name ASC) RowNum FROM OCCUPATIONS o), Doctors AS (SELECT o.

Sql hackerrank occupations FROM ( SELECT name, occupation, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS row_number FROM OCCUPATIONS )dt PIVOT ( MAX(name) FOR [Occupation] IN ( [Doctor], [Professor], [Singer], [Actor] ) Pivot the Occupation column so the Name of each person in --- my sql solution. 0 | Create a HackerRank account Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. name) Pr from occupations P where occupation = 'Professor') , Ds As (select D. SELECT Doctor, Professor, Singer, Actor . Create a HackerRank account My solution for MSSQL. Create a HackerRank account Finally the code aggregates the rows together using the group by RowNumber and the min statement. We use cookies to ensure you have the best browsing experience on our website. SQL. I use this query With Ps As (select P. Thus, we would not be able to get the desired row count in our output. NAME, SingTable. SELECT MAX(CASE WHEN occupation = 'Doctor' THEN name ELSE NULL END) AS Doctor, MAX(CASE WHEN occupation = 'Professor' THEN name ELSE NULL END) AS Professor, MAX(CASE WHEN occupation = 'Singer' THEN name ELSE NULL END) AS Singer, MAX(CASE WHEN occupation = 'Actor' THEN name ELSE NULL END) AS Actor FROM SQL. Create a HackerRank account Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. WITH add_id AS( SELECT *, ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name) as id FROM occupations), doctor AS( SELECT id, name FROM add_id WHERE occupation = 'doctor' ), professor AS( SELECT id, name FROM add_id WHERE occupation = 'professor' ), singer AS( SELECT id, name FROM add_id WHERE occupation = WITH RankedOccupations AS ( SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn FROM OCCUPATIONS ) SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END) AS Doctor, MAX(CASE WHEN Occupation = 'Professor' THEN Name ELSE NULL END) AS Professor, MAX(CASE Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. SELECT MAX(CASE WHEN Occupation='Doctor' THEN Name END) AS Doctor, MAX(CASE WHEN Occupation='Professor' THEN Name END) AS Professor, MAX(CASE WHEN Occupation='Singer' THEN Name END) AS Singer, ROW_NUMBER() OVER() will return the number of the row for each Occupation, combined with GROUP BY Sub. from (select name, occupation, rownumber() over (partition by occupation order by name asc) rn from occupations) Works with Mysql: # with base_tab as ( select case when occupation ='Doctor' then name else null end as doctor, case when occupation='Professor' then name else null end as professor, case when occupation='Singer' then Name else null end as singer, case when Occupation='Actor' then Name else null end as actor from OCCUPATIONS), doctor_tab as ( select row_number() Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor, MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor, MAX(CASE WHEN Occupation = 'Singer' THEN Name END) AS Singer, MAX(CASE WHEN SQL. You are viewing a single comment's thread. Modified 1 month ago. NAME, ActTable. 2 months ago + 1 comment '#1: WITH t1 AS (SELECT name AS doctor, ROW_NUMBER OVER (ORDER BY name) AS row_num FROM occupations WHERE my way : with doctor_table as (select row_number() over (order by name) as doctor_id, name,occupation from occupations where occupation = 'doctor' ), singer_table as (select row_number() over (order by name) as singer_id, name,occupation from occupations where occupation = 'singer' ) , actor_table as (select row_number() over (order by name) as Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. WITH RankedOccupations AS ( SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn FROM OCCUPATIONS ) SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor, MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor, MAX(CASE WHEN Occupation = SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END) AS Doctor, MAX(CASE WHEN Occupation = 'Professor' THEN Name ELSE NULL END) AS Professor, MAX(CASE WHEN Occupation = 'Singer' THEN Name ELSE NULL END) AS Singer, MAX(CASE WHEN Occupation = 'Actor' THEN Name ELSE NULL END) AS Actor Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. ROW_NUMBER(): Assigns a unique number to each row. In this case, the MAX function is being used to retrieve the maximum value Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. name FROM occupations) AS tab1 GROUP BY name_rank ORDER BY name_rank; SQL. WITH temp AS ( SELECT IIF(Occupation = 'Doctor', Name, NULL) Doctor , IIF(Occupation = 'Professor', Name, NULL) Professor , IIF(Occupation = 'Singer', Name, NULL) Singer , IIF(Occupation = 'Actor Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Pivot occupations. name) Sr from occupations S where occupation = 'Singer'), AA As ( select A. @arati1626 @himanshimathpal1 To clarify this, first you need to create row number of each record partitioned by its occupation in the entity Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. = 'Singer' then name else NULL end as Singer, case when OCCUPATION = 'Actor' then name else NULL end as Actor from OCCUPATIONS order by name ) Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Source: HackerRank. For me it always helps to turn the "subquery" into its own query and take a look at its output, as follows: SELECT Name, Occupation, (ROW_NUMBER() OVER( PARTITION BY Occupation ORDER BY Name )) AS rn FROM Occupations SQL. Very easy to understand solution: with doc as ( select Name as Doctor,row_number()over(order by name) as rn from occupations where occupation = 'Doctor' ), prof as ( select Name as Professor,row_number()over(order by name) as rn from occupations where occupation = 'Professor' ), sing as ( select Name as Singer,row_number()over(order by name) as rn from WITH name_ranked AS ( SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS row_name FROM OCCUPATIONS GROUP BY Occupation, Name ) SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor, MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor, Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Most ones can solve the problem but i can't. with base as ( select * , row_number() over (partition by Occupation order by Name)as checks from OCCUPATIONS) Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Return to all comments → (Partition by occupation Order By name) as row_num from occupations) as SQL. doctor, b. recency | 2041 Discussions| Please Login in order to post a comment. Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. row2: the second in Doctor; the secondin Professor; the second in Singer; the second in Actor. The output column headers should be Doctor, Professor, Codes of Algorithms/Coding Competitions on Hackerrank with Python, JavaScript, C++ and SQL - ynyeh0221/HackerRank Try choosing MYSQL Server instead of MYSQL, the error gets resolved there. Name ASC) RowNum FROM OCCUPATIONS o WHERE o. Viewed 20k times 2 . Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Really good stuff. WITH Ranked AS ( SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn FROM Occupations ) SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor, MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor, MAX(CASE WHEN Occupation = SQL. For instance, consider the following example: SQL. CREATE VIEW maxtable AS SELECT * FROM ((SELECT COUNT (*) AS max, "Doctor" AS Occupation FROM OCCUPATIONS WHERE Occupation = "Doctor") UNION ALL SQL. actor from (SELECT b. My SQL Server Code. Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. - raleighlittles/HackerRank-SQL Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. name) Dr from occupations D where occupation = 'Doctor'), Ss As(select S. Once you complete step 3, add "ORDER BY Name" (Refer above code on where to add Order by clause). For MS SQL Server: SELECT [Doctor], Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Return to all comments → , row_number() over (partition by occupation order by name asc) rn from occupations) abc group by rn order by rn; Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. professor FROM (SELECT name AS doctor, ROW_NUMBER() OVER (ORDER BY name) AS r1 FROM occupations WHERE occupation = 'doctor' ) AS a right join (SELECT name AS professor, ROW_NUMBER() OVER (ORDER BY name) AS r2 FROM occupations WHERE Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Create a HackerRank account Can some one explain the code,this solution by YujiShen has really made me thinking how SQL query works ,what I mean is which part of query is evaluated first and how does a query iterate through rows. Problem. Occupation = ' Doctor '), Professors AS (SELECT o. So add Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. name_group_rank), (SELECT Name FROM partitioned_cte WHERE Occupation = Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. my solution for MS SQL: select doctor, professor, singer, actor from (select occupation, name, RANK () Create a HackerRank account Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. This ID column is special though, it gets recalculated for every group of Occupation values, this makes sure that while the SELECT clause is running, it will SQL. NAME, ProfTable. Create a HackerRank account For MySQL. Create a HackerRank account MS sql server. SELECT MAX(Doctor) AS Doctor, MAX(Professor) AS Professor, MAX(Singer) AS Singer, MAX(Actor) AS Actor FROM ( SELECT CASE WHEN Occupation = 'Doctor' THEN Name END AS Doctor, CASE WHEN Occupation = 'Professor' THEN Name END AS Professor, CASE WHEN Occupation = 'Singer' THEN Name END AS Singer, CASE WHEN Occupation = 'Actor' THEN SQL. WITH RankedOccupations AS ( -- Assign a row number to each name based on their occupation and sort them alphabetically SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNumber FROM OCCUPATIONS ) -- Pivot the table by occupations SELECT I think that's because you're only replacing the positions of each name that is not in the corresponding occupation column with NULL. Occupations. YujiShen. Submissions. Here I see the answer is quite tough for me to solve. Leaderboard. Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective SET @max_rows:= (SELECT COUNT(DISTINCT Occupation) FROM OCCUPATIONS);. As stated below 'min()/max() will return a name for specific index and specific occupation. Hackerrank SQL challenge: Occupations. PARTITION BY Occupation: Resets the row number for each occupation group. Discussions. select [Doctor],[Professor], Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Return to all comments →. 9 years ago + 16 comments. Return to all comments → ROW_NUMBER() over (partition by occupation order by name) as rowno from occupations )as subtb group by rowno order by rowno Pretty much what @buyantugs_luu said. select Doctor, Professor, Singer, Actor from (select *, row_number over Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. These recursive functions you constructed {@r1:=@r1+1} is basically the same as creating a rank column partitioned by Occupation:with cte as ( select RANK() OVER (PARTITION BY Occupation ORDER BY Name) as Rank, case when Occupation='Doctor' then Name else null end as ==== Steps 1 ==== select name, Doctor, Professor, Singer, Actor from occupations pivot (count (occupation) for occupation in (Doctor, Professor, Singer, Actor)) as p Out put: Aamina 1 0 0 0 Ashley 0 1 0 0 Belvet 0 1 0 0 Britney 0 1 0 0 Christeen 0 0 1 0 Eve 0 0 0 1 Jane 0 0 1 0 Jennifer 0 0 0 1 Jenny 0 0 1 0 Julia 1 0 0 0 Ketty 0 0 0 1 Kristeen Oracle. I translated your solution to something that works in MS SQL. RowNum the results will come ordered, try executing only the subselect and see the output: Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. I even can understand the answer given in discussion section. In the first statement it doesn't matter if Min or Max is used as long as the occupations are ordered by Name, they are used to select actual values rather than NULL. Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. Sort by. HackerRank's code block convert "@r1" into "(/r1)", so all the assignment are not correct. Step2 : Recombine rows : use group by (need at least one same property ) Because we want the result looks like this row1: the first in Doctor; the first in Professor; the first in Singer; the first in**Actor**. Create a HackerRank account MYSQL: this code contains 2 CTEs, first grouping by occupation and numbering names by alphbetical order second CTE It uses a CASE statement within the MAX function to conditionally aggregate names based on the Occupation. Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective SQL. but we still need aggregate function to group by the name_rank and print out the same stuff in our case. chat-gpt will refers you dynamic queries or some ways like my query because there is no pivot operator like Microsoft SQL Server in MySQL. thituyetmaitran1. I used a similar query, but my order for the professors is incorrect. Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective SELECT MIN(CASE WHEN occupation = 'Doctor' THEN name END) AS Doctor, MIN(CASE WHEN occupation = 'Professor' THEN name END) AS Professor, MIN(CASE WHEN occupation = 'Singer' THEN name END) AS Singer, MIN(CASE WHEN occupation = 'Actor' THEN name END) AS Actor FROM ( SELECT name, occupation, ROW_NUMBER() OVER Simply i wrote this code in SQL Server and its working fine: Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. name, rank() over( order Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Create a HackerRank account SQL. Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively. Annotated solutions to HackerRank's SQL domain questions. this will work for oracle sql : SELECT MAX(CASE WHEN occupation = 'Doctor' THEN name ELSE NULL END) AS Doctor, MAX(CASE WHEN occupation = 'Professor' THEN name ELSE NULL END) AS Professor, MAX(CASE WITH RankedOccupations AS ( SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn FROM OCCUPATIONS ) This approach is easy to use and understandable. The use of the MAX function in this SQL query is to retrieve the maximum value of a given column for each group defined in the GROUP BY clause. name, rank() over( order by D. with p as (select case when occupation = 'doctor' then name end as 'doctor', case when occupation = 'professor' then name end as 'professor', case when occupation = 'singer' then name end as 'singer', Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. NAME FROM (SELECT OCCUPATION, [NAME], ROW_NUMBER() OVER(PARTITION BY OCCUPATION ORDER BY [NAME] ASC) AS ID FROM OCCUPATIONS WHERE OCCUPATION = 'Doctor') As DocTable FULL OUTER JOIN Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Then same property I need in result is rollnumber . THIS IS FOR SQL SERVER SELECT [Doctor],[Professor],[Singer],[Actor] FROM( SELECT ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME) AS [ROWNUMBER], * FROM OCCUPATIONS) AS TEMP_TABLE PIVOT ( MIN(NAME) FOR OCCUPATION IN ([Doctor],[Professor],[Singer],[Actor]) ) AS PIVOT_TABLE Pivot the WITH RankedOccupations AS ( SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNum FROM OCCUPATIONS ), PivotedOccupations AS ( SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor, MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor, WITH ranked AS ( SELECT name, occupation, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS rn FROM OCCUPATIONS ), pivoted AS ( SELECT MAX(CASE WHEN occupation = 'Doctor' THEN name END) AS Doctor, MAX(CASE WHEN occupation = 'Professor' THEN name END) AS Professor, MAX(CASE WHEN SQL Server WITH DOCTOR AS ( SELECT NAME , ROW_NUMBER () OVER ( ORDER BY NAME ASC ) NUM FROM OCCUPATIONS WHERE OCCUPATION = ' DOCTOR ' GROUP BY NAME ), PROFESSOR AS ( SELECT NAME , ROW_NUMBER () OVER ( ORDER with temp as (select* ,ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS id from OCCUPATIONS ) , pvt_temp as (select * From temp pivot (max(name) for occupation in (Doctor, Professor, Singer, Actor) )as pvtCols )select Doctor, Professor, Singer, Actor from pvt_temp Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. SELECT DocTable. The output column /* Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. WITH partitioned_cte AS (SELECT Name, Occupation, row_number OVER(PARTITION BY Occupation ORDER BY Name) AS name_group_rank FROM OCCUPATIONS) SELECT (SELECT Name FROM partitioned_cte WHERE Occupation = 'Doctor' AND name_group_rank = t. Doctor and other occupations columns contain only a single value for each ranks, so you can reduce null value by MAX or MIN aggregate function with group by rank value. Ask Question Asked 2 years ago. WITH SortedOccupations AS ( SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNum FROM Occupations ) SELECT [Doctor SELECT MAX(CASE WHEN occupation='doctor' THEN NAME ELSE NULL END) AS doctor, MAX(CASE WHEN occupation='professor' THEN NAME ELSE NULL END) AS professor, MAX(CASE WHEN occupation='singer' THEN NAME ELSE NULL END)AS singer, MAX(CASE WHEN occupation='actor' THEN NAME ELSE NULL END)AS actor FROM ( Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. The output column headers should be SELECT CONCAT(GROUP_CONCAT(IF(Occupation = 'Doctor', Name, NULL) ORDER BY Name SEPARATOR ',')) AS Doctor, CONCAT(GROUP_CONCAT(IF(Occupation = 'Professor', HackerRank SQL Interview Question: Level Medium. professor,f. then any aggregate function which can print out same stuff in works here. WITH LEBALTABLE AS Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. MAX is used to pick the name in a grouped row. Create a HackerRank account SQL SERVER. SQL Server solution: WITH Numeradas AS Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. doctor,e. Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. SELECT Occupation, MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor, MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor, MAX(CASE WHEN Occupation = 'Singer' THEN Name END) AS Singer, Group by is used to convert columns to rows. -- Step 1: Assigning Row Numbers within each Occupation WITH NameLists AS ( SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS NameOrder FROM Occupations ) Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective in SQL SERVER, code is . Name ASC) RowNum FROM OCCUPATIONS o), Doctors AS (SELECT o. (PARTITION BY OCCUPATION ORDER BY name) AS rn FROM OCCUPATIONS WHERE occupation = ' Doctor '), professors AS (SELECT name AS professorName, ROW_NUMBER SQL. Create a HackerRank account Be part of a 23 million-strong community of developers. r2, a. Occupation = ' Professor '), I think this is a much simpler MySQL solution: CREATE VIEW OccupationsView AS SELECT ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS row_num, Occupation, Name FROM OCCUPATIONS; SELECT MAX(IF(Occupation = 'Doctor', Name, NULL)) AS Doctor, MAX(IF(Occupation = 'Professor', Name, NULL)) AS Professor, You basicly create an CTE that includes the OCCUPATIONS table plus an added ID column, this is needed for sorting the results correctly, this is the CTE I called KEYED_OCCUPATIONS. If we don't use an aggregation function, we would be forced to group the query further by Name and Occupation. Create a HackerRank account SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor, MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor, MAX(CASE WHEN Occupation = 'Singer' THEN Name END) AS Singer, MAX(CASE WHEN Occupation = 'Actor' THEN Name END) AS Actor FROM ( SELECT Name, Occupation, ROW_NUMBER() SQL. SELECT rank, MAX(CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END) AS select e. Create a HackerRank account with doctor as ( select name as name, row_number() over (order by name) as row_num from occupations where occupation = 'doctor' order by name asc ), prof as( select name as name, row_number() over (order by name) as row_num from occupations where occupation = 'professor' order by name asc ), singer as( select name as name, row_number() over Except for it's giving Occupation name as the 1st column, I don't understand why this is not working, can some explain please. name, rank() over( order by S. Advanced Select. The output column headers should be Doctor, Professor, Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. Group by ranking; Use case to create each column (Doctor, Professor, Singer, and Actor) Use an aggregation function to include the cases in the SELECT statement There is only one value per group so its not relevant whether it is min or max. Since some of us here may not be fimilar with sql, I'll start with where I left so you get the whole picture. SELECT MAX(CASE WHEN occupation = 'doctor' THEN name ELSE NULL END) AS doctor, MAX(CASE WHEN occupation = 'professor' THEN name ELSE NULL END) AS professor, MAX(CASE WHEN occupation = 'singer' THEN name ELSE NULL END) AS singer, MAX(CASE WHEN occupation = 'actor' THEN name ELSE NULL END) AS actor FROM ( SQL SERVER. select doctor, professor, singer, actor . You have to change them back. name, rank() over( order by P. . singer,f. Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Name, ROW_NUMBER OVER (ORDER BY o. MS SQL Code: SELECT Doctor, Professor, Singer, Actor FROM ( SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) as rn FROM Occupations ) AS src_table PIVOT ( MAX(Name) WITH ctc AS (SELECT name, occupation, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS row_num FROM occupations ) SELECT MAX(CASE WHEN occupation ='Doctor' THEN name END)AS Doctor, MAX(CASE WHEN occupation ='Professor' THEN name END)AS Professor, MAX(CASE WHEN occupation ='Singer' THEN select (select name from occupations where occupation = 'Doctor' order by name) as doctor , (select name from occupations where occupation = 'Professor' order by name MySQL: SELECT MAX(CASE WHEN Occupation = 'Doctor' Then Name END) AS 'Doctor', MAX(CASE WHEN Occupation = 'Professor' Then Name END) AS 'Professor', MAX(CASE WHEN Occupation = 'Singer' Then Name END) AS 'Singer', MAX(CASE WHEN Occupation = 'Actor' Then Name END) AS 'Actor' FROM (SELECT Name, Occupation, Oracle SQL: SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END) AS Doctor, MAX(CASE WHEN Occupation = 'Professor' THEN Name ELSE NULL END) AS Professor, MAX(CASE WHEN Occupation = 'Singer' THEN Name ELSE NULL END) AS Singer, MAX(CASE WHEN Occupation = 'Actor' THEN Name ELSE NULL END) AS Actor FROM ( Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. polczdp dlvihc wyfviu dpj rgz cydoq ilzdz fzhzi yzxhl kejsh