Sql hackerrank occupations 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. Since some of us here may not be fimilar with sql, I'll start with where I left so you get the whole picture. I even can understand the answer given in discussion section. I use this query With Ps As (select P. PARTITION BY Occupation: Resets the row number for each occupation group. 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. 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. 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. name FROM occupations) AS tab1 GROUP BY name_rank ORDER BY name_rank; SQL. Name ASC) RowNum FROM OCCUPATIONS o), Doctors AS (SELECT o. 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. 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. 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. 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. 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. Return to all comments → (Partition by occupation Order By name) as row_num from occupations) as SQL. Annotated solutions to HackerRank's SQL domain questions. Create a HackerRank account Finally the code aggregates the rows together using the group by RowNumber and the min statement. select [Doctor],[Professor], Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. So add Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. 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. 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. Leaderboard. Modified 1 month ago. 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. Pivot occupations. Create a HackerRank account SQL SERVER. 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. 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. 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. = '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. CREATE VIEW maxtable AS SELECT * FROM ((SELECT COUNT (*) AS max, "Doctor" AS Occupation FROM OCCUPATIONS WHERE Occupation = "Doctor") UNION ALL SQL. 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. NAME, SingTable. -- 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. Create a HackerRank account My solution for MSSQL. NAME, ActTable. 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. 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. 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. NAME, ProfTable. ROW_NUMBER(): Assigns a unique number to each row. Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Really good stuff. Create a HackerRank account SQL. Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective in SQL SERVER, code is . Viewed 20k times 2 . Ask Question Asked 2 years ago. Create a HackerRank account Be part of a 23 million-strong community of developers. 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 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. Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. professor,f. name, rank() over( order by S. Name ASC) RowNum FROM OCCUPATIONS o WHERE o. 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. SELECT DocTable. name) Sr from occupations S where occupation = 'Singer'), AA As ( select A. 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. Create a HackerRank account Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Create a HackerRank account For MySQL. Most ones can solve the problem but i can't. Once you complete step 3, add "ORDER BY Name" (Refer above code on where to add Order by clause). 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. HackerRank's code block convert "@r1" into "(/r1)", so all the assignment are not correct. Occupations. Problem. 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. r2, a. Hackerrank SQL challenge: Occupations. We use cookies to ensure you have the best browsing experience on our website. thituyetmaitran1. WITH LEBALTABLE AS 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. 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. 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. 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. 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. 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 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. - raleighlittles/HackerRank-SQL Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. 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. Source: HackerRank. Name, ROW_NUMBER OVER (ORDER BY o. Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. SELECT rank, MAX(CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END) AS select e. 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. 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. SQL Server solution: WITH Numeradas AS Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Sort by. 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. name, rank() over( order by P. Then same property I need in result is rollnumber . For MS SQL Server: SELECT [Doctor], Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. SELECT Doctor, Professor, Singer, Actor . SQL. 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. If we don't use an aggregation function, we would be forced to group the query further by Name and Occupation. For instance, consider the following example: SQL. 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. Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective SQL. then any aggregate function which can print out same stuff in works here. 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. 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. Create a HackerRank account MS sql server. 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);. . recency | 2041 Discussions| Please Login in order to post a comment. 9 years ago + 16 comments. select doctor, professor, singer, actor . Submissions. Return to all comments →. Here I see the answer is quite tough for me to solve. 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. The output column headers should be Doctor, Professor, Singer, and Actor, respectively. 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**. Thus, we would not be able to get the desired row count in our output. doctor, b. 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. name, rank() over( order 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. doctor,e. singer,f. YujiShen. row2: the second in Doctor; the secondin Professor; the second in Singer; the second in Actor. The output column /* Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. Advanced Select. @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. name) Dr from occupations D where occupation = 'Doctor'), Ss As(select S. 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. I used a similar query, but my order for the professors is incorrect. but we still need aggregate function to group by the name_rank and print out the same stuff in our case. You have to change them back. 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. 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. 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. 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. actor from (SELECT b. 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. As stated below 'min()/max() will return a name for specific index and specific occupation. Occupation = ' Doctor '), Professors AS (SELECT o. My SQL Server Code. 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. 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. 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. You are viewing a single comment's thread. (PARTITION BY OCCUPATION ORDER BY name) AS rn FROM OCCUPATIONS WHERE occupation = ' Doctor '), professors AS (SELECT name AS professorName, ROW_NUMBER SQL. 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. 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. Discussions. name, rank() over( order by D. 0 | Create a HackerRank account Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. I translated your solution to something that works in MS SQL. yxfvr nyoh oieqbcy hemde fmih pvagwt icm vxila axgzv jejvpx