Hackerrank pivot table occupation. This approach is easy to use and understandable.

Hackerrank pivot table occupation ; The second column is an alphabetically ordered list of Professor names. Navigation Menu Toggle navigation. I saw this site while Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Blog; Scoring Unlock the full potential of your data with the art of pivoting in SQL. We want to pivot this table by Occupation such that each Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Source: HackerRank. Find and fix vulnerabilities Codespaces. HackerRank | Prepare; (name) for each Row ID under each occupation. Note: Print NULL when there are no more names corresponding to an Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Provide multiple ways of specifying calculation logic to cover both simple and more sophisticated requirements. Yes, you only have the columns Name and Occupation - if you pivot by Occupation and aggregate the name only one row will be returned. The output column headers should be Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Please signup or login in order to view this challenge. NAME, SingTable. Please read our cookie policy for more information about how we use cookies. Ok. Contribute to imrand-dev/hackerrank development by creating an account on GitHub. {Preceding table} PIVOT( Aggregate function, FOR(Data to become column headings), IN(Set of column headings)) Please follow us https://www. Step 2: Pivot the data. . SELECT Doctor, Professor, Singer, Actor. Note: Print NULL when there are no more names corresponding to an occupation. [last pivoted column]) ) AS alias for the pivot table optional ORDER BY clause; Firstly in this outer SELECT im basically naming my custom columns for the PIVOT Table. If more than one Occupation has the same [occupation_count], they should be ordered For MS SQL SERVER: with CTE AS ( select Name , Occupation , dense_rank() over (partition by Occupation order by Name) rn from OCCUPATIONS ) select [Doctor],[Professor],[Singer],[Actor] from (select Name , Occupation , rn from CTE) AS Source_table PIVOT ( Max(Name) FOR OCCUPATION IN ([Doctor],[Professor],[Singer ],[Actor]) ) AS P order by rn; Reshpe Occupation into 4 columns with their index in each occupation, this leads to Temp table (which has many NULL). NAME, ProfTable. I think we can remove one more select from here: SELECT Doctor, Professor, Singer, Actor FROM ( SELECT name AS name, row_number() over (PARTITION BY occupation ORDER BY name) AS rownum, occupation as occ FROM occupations when ever they ask pivot, pivot is relational operator which converts rows data of table to column data. " Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. Input Format: Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Name) AS rank FROM Occupations AS a Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. 0 | permalink. 0 | Permalink. This is about the defination of pivot Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. SELECT a. Occupation = b. blog; scoring; environment; faq; about us; support; careers; In this HackerRank Functions in SQL problem solution, Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed Note: Print NULL when there are no more names corresponding to an 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. Ok | Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. You switched accounts on another tab or window. The output column Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. Occupations contain four occupation types: Doctor, Professor, Singer and Actor. You can also think of this as: when you pivot a table, any column other than the one you perform a function on is added to the grouping used to create the pivot table. So in advance; I'm sorry if this is too basic stuff or my query is too messy. Please read our cookie policy for HackerRank SQL Interview Question: Level Medium. 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 Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. I'm new to sql and this community. Thanks! Bonus questions 1. Please check the The table structure , the table in which you are trying to insert. We use cookies to ensure you have the best browsing experience on our website. The Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. be/7C8wlTNUQzAinput an integer Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Hackerrank Problems Solutions. Blog; Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. 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 Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. ms sql server query SELECT [Doctor], [Professor], [Singer], [Actor] FROM ( SELECT Occupation, Name, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RN FROM OCCUPATIONS ) AS T PIVOT ( MAX(Name) FOR Occupation IN ([Doctor], [Professor], [Singer], [Actor]) ) AS P ORDER BY RN; I need to change this table here . Create a HackerRank account Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Ok | SQL Problem Statement: Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. com/dev. The PIVOT operation: Takes the source_tb table as input. Hackerrank occupations problem link. 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' Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. 0. Pivot the Occupation column in OCCUPATIONS so that Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. AS SOURCE_DATA PIVOT ( MAX(NAME) FOR OCCUPATION IN ([Doctor],[Professor],[Singer],[Actor]) ) AS PIVOT_TABLE. Whether you’re a data analyst, a software engineer, or just a SQL enthusiast, mastering this technique can turn your wide Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. SQL Server has the Pivot table feature which we can use directly. The output column headers should be Doctor, Professor, Singer, and Actor, Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. r; excel; Inside you will find the solutions to all HackerRank SQL Questions. 'Professor' AS Professor, 'Singer' AS Singer, 'Actor' AS Actor) ) AS pivot_table ORDER BY row_num; It might help to think of it in terms of writing it without PIVOT. Without the source table the PIVOT function would not know which table's row has to be converted to columns. Table preparation. shravyakulal. The pivottabler package aims to:. Blog; Scoring; Environment; FAQ; About Us; Support; Careers; Terms Of Service select * from (select row_number over (partition by occupation order by name) as rn, occupation, name from occupations group by occupation, name) as oc pivot (max (name) for occupation in ([doctor],[professor],[singer],[actor])) as pivot_table. microsoft. Create a HackerRank account Be part of a 23 million-strong community of developers. and this is the desired solution ! ` 12 | Permalink. It asks: Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. I am trying to understand the solution to hackerrank question below. 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) FOR Occupation IN (Actor, Doctor, Professor, Singer) ) AS pvt_table ORDER BY rn; Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. SELECT HackerRank,SQL,Advanced Select,Occupations,MySQL,DB2,Hive,APDaga,DumpBox,PIVOT,SET,Akshay Daga,CASE WHEN,GROUP BY,ORDER BY,List of Names by profession Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Eventually, I do want to be able to do more complicated pivots where I SUM or take MAX. For each row, if the occupation is the same as the previous row's occupation, we increment @rn. [Professor], [Singer] ,[Actor] FROM ( SELECT * FROM occupations ) AS source_table PIVOT ( MAX(name) FOR occupation IN ([Doctor],[Professor], [Singer] ,[Actor]) ) AS pivot_table; I agree to HackerRank's Terms of --Pivot the OCCUPATIONS table and display names alphabetically under each occupation. 1. So now the fun part. The output column headers should be Doctor, Professor, Singer, and Actor, respectively. We will go by our usual step by step Approach - Step 1: We will write the inner Query to fetch the required data Step 2 : We will pivot the data we Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. SELECT DocTable. SET @SQL = ' SELECT * into #temp FROM ( SELECT row_number() over (order by name) as id, [Name], [Occupation] FROM Occupation_data ) AS cp PIVOT ( min([Name]) FOR [Occupation] IN (' + @cols + ') ) AS up; Select row_number() over (order by doctor asc ) as id1 ,* into #temp_1 from #temp where Doctor is not null Select row_number() over (Order by Occupations. (Name) for Occupation in (Doctor, Professor, Singer, Actor)) as pivot_table; 0 | Permalink. HackerRank,SQL,Advanced Select,Occupations,MySQL,DB2,Hive,APDaga,DumpBox,PIVOT,SET,Akshay Daga,CASE WHEN,GROUP BY,ORDER BY,List of Names by profession Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. I have used these particular functions before but I am revising after a gap I am not able to understand it clearly. So we get all the names. CREATE TABLE test ( DataType VARCHAR(10), Value VARCHAR(10) ); INSERT INTO test VALUES ('A', 'String'), ('A', 'Int'), ('B The pivottabler package enables pivot tables to be created with just a few lines of R. You signed out in another tab or window. sql at main · Pavith19/HackerRank-SQL-Challenges-Solutions /* Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding I used a similar query, but my order for the professors is incorrect. Please read our cookie policy for more (PARTITION BY Occupation ORDER BY Name) AS rn FROM OCCUPATIONS ) AS pivot_table GROUP BY rn; Data at Occupations Table. Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. name occupation samantha doctor julia actor maria actor meera singer ashley professor ketty professor christen professor jane actor penny doctor priya singer Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output should consist of four columns ( Doctor , Professor , Singer , and Actor Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. min()/max() will return a name for specific index and specific occupation. ROW_NUMBER() I've used, just to act as an id for the source table grouped by Occupation I have this OCCUPATIONS table as below and I need to pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. HackerRank | Prepare; Certify; Compete; Apply; Hiring developers? (pivoting a table) so here is what you need to do: 1- what the f Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Blog; Scoring; Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. I'd also be interested in how to do this without PIVOT if there is a simple way. community/Thanks for watching us Dev19🖤 I also encountered this problem on HackerRank. Sample Output. Provide an easy way of creating pivot tables, without requiring the user to specify low-level layout logic. Occupation, a. Note: Print NULL when there are no more names corresponding to an Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. CREATE TABLE #Occupations ([Name] varchar(500), [Occupation] varchar(100)) ; INSERT INTO #Occupations ([Name], [Occupation]) VALUES ('SAMANTHA', 'DOCTOR'), ('JULIA The below SQL query works fine on SQL Server. 2. It look like this https: Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Skip to content. Blog; Scoring; Environment; FAQ; About Us; Support; Careers; Terms Of Service This approach is easy to use and understandable. I've tried dplyr, tidyr but I cant get what I want. Reload to refresh your session. Good, gentle, articles to PIVOT? I clearly haven't gotten it through my thick head. The goal is to transform the data, from one data format into a different str Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Sign in Product Actions. If it is different, we reset @rn to 1. for each Row ID under each occupation. SO, the table formed here will be : RANK Doctor Professor Singer Actor 1 Maria Jenny Ashely Meera 2 Jane Samantha Christeen Priya 3 Julia NULL Ketty NULL. The output column headers should be Doctor, Professor, Singer, Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. instagram. The OCCUPATIONS table is described as follows: Occupation will only contain one of the following values: Doctor, Professor Here is the query to pivot the Occupation column and display the names alphabetically under each Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. (PARTITION BY OCCUPATION ORDER BY NAME) AS ROW_ORDER FROM OCCUPATIONS) AS SOURCE_TABLE PIVOT (MAX (NAME) FOR OCCUPATION IN ([Doctor], [Professor], [Singer], [Actor])) AS PIVOT_TABLE; 0 | Permalink. Jenny Ashley where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. Name > b. Input Format The OCCUPATIONS Name) Rown from Occupations t) Pivot (min (name) for Occupation in (' Doctor ' Doctor, ' Professor ' Professor, ' Singer ' Singer, ' Actor ' Actor)) order by Rown; *(A little simplified version) In the subquery part I've numerated the rows in every Explanation. By adding the row_number the aggregation will not reduce the output to a single row since each row has a differnet row_number – Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Please read our cookie policy for Occupations-Hackerrank Print NULL when there are no more names corresponding to an occupation. Please read our cookie policy for Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. ; The fourth column is an alphabetically ordered list of Actor names. The output column headers should be doctor, professor, singer, and actor, respectively. Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers; Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand; OverflowAI GenAI features for Teams; OverflowAPI Train & fine-tune LLMs; Labs The future of collective knowledge sharing; About the company This repository contains solutions to all the HackerRank SQL Practice Questions - HackerRank-SQL-Challenges-Solutions/Advanced Select/Occupations. For each occupation (Doctor, Professor, Singer, Actor), it takes the MAX(name) value and places it in the corresponding column. The output column headers should be Doctor, Professor, Singer, and Actor, respectively. The OCCUPATIONS table is described as follows: Occupation will only contain one of the following Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. ; The empty cell data for columns with less than the maximum number of names per occupation Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. The first column is an alphabetically ordered list of Doctor names. Create a column (ranking) that rank names alphabetically by occupation; Stored as a CTE; Pivot occupations. as basetable pivot (Max(name) for Occupation in([Doctor], [Professor],[Singer],[Actor])) as pivot_table; 0 | Parent Permalink. Pivot Table Using MySQL. Instant dev environments Contribute to gagank44/Hackerrank-SQL development by creating an account on GitHub. query to pivot data in mysql database. Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. Host and manage packages Security. OVER (PARTITION BY Occupation ORDER BY NAME) as row_num FROM OCCUPATIONS) AS TEMP_TABLE PIVOT (Min (NAME), FOR OCCUPATION IN (Doctor, Professor, Singer, Actor)) AS PIVOT_TABLE. Next, we want to Previous Video Links: program to read two numbers from the keyboard and display the larger value on the screen: https://youtu. You signed in with another tab or window. How to display results without column headers? 3. select [Doctor] as Doctor, [Professor] as Professor, [Singer] as Singer, [Actor] as Actor from (select name, occupation, ROW_NUMBER() OVER ( PARTITION BY Occupation ORDER BY Name) as row from occupations) occup pivot (min(name) for occupation in ([Doctor], [Professor] ,[Singer],[Actor])) as pivottable; Hello, I am new to SQL Server and learning things with the help of Hacker Rank Question - Occupations | HackerRank query (with this we get all the rows)- SELECT [Doctor], [Professor], [Singer], [Actor] FROM (SELECT ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME) [RowNumber], * FROM OCCUPATIONS ) AS tempTable PIVOT Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. max function will tell max number of times occupation is repeated , using case function we are giving condition and then selecting columns , rows partitioning by rows occupation , ordering by name, and we are grouping by MS SQL (with PIVOT): SELECT * , ROW_NUMBER () OVER ( PARTITION BY occupation ORDER BY name ) AS rank INTO # sorted_occupations FROM occupations ; SELECT Doctor , Professor , Singer , Actor FROM # sorted_occupations PIVOT ( MAX ( I want to create a pivot table like this (this was made in Excel) As you can see, ID is classified by year and month and their "Values" are being sum. FOR Occupation IN ( [Doctor], [Professor], [Singer], [Actor]) ) AS pivot_table)a. This essentially numbers each name within its occupation category (1, 2, 3,). Input Format. 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. Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding ==== 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 WITH cte AS ( SELECT * FROM ( SELECT RANK() OVER (PARTITION BY occupation ORDER BY name) AS rank, occupation, name FROM occupations) AS oc_n PIVOT ( MAX(name) FOR occupation in (Doctor,Professor, Singer , Actor) ) AS pivot_table) Problem Statement: Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. The output column Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. HackerRank | Prepare; Certify; (name) for occupation in ([Doctor], [Professor],[Singer],[Actor]) ) as pivot_table; 0 | Permalink You signed in with another tab or window. I am completing this challenge from HackerRank. Automate any workflow Packages. NAME, ActTable. Contribute to rishabh8cs/Hackerrank development by creating an account on GitHub. Please read our cookie policy for more I searched for "Pivot mySql" based on the keyword in question. Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically Query to pivot the Occupation column in Occupation table so that each name is sorted alphabetically and displayed underneath its corresponding Occupation. ; The third column is an alphabetically ordered list of Singer names. Pivot the occupation column in occupations so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. Then group Temp by index and select corresponding Name for each Occupation. MySQL - Pivot columns into rows. over (partition by occupation order by name) as ordinal from occupations where occupation = 'Actor' ; drop table if exists professors; create temporary table professors ( name varchar(50) ,occupation varchar(50) ,ordinal int ); insert into professors select name ,occupation ,row_number() over Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017 ROW_NUMBER () OVER (PARTITION BY Occupation ORDER BY Name) row_num, [Name], [Occupation] FROM Occupations ) AS Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column "Learn to pivot the Occupation column in OCCUPATIONS table on HackerRank SQL, sorting Names alphabetically under Doctor, Professor, Singer, and Actor headers. Any advice would be useful. select [Doctor], Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. 3 years ago + 0 comments. --Pivot the OCCUPATIONS table and display names alphabetically under each occupation. 19. count parallely means by this I wanna show the first of all the occupation's name together so that it can take a Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Pivots the data, transforming the occupation column values into individual columns. 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 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 , -- Display Doctor names MAX ( CASE WHEN Occupation = ' Professor ' THEN Name END ) AS Professor , -- Display Professor names MAX ( CASE WHEN Occupation = ' Singer ' THEN Name END ) Yes, little bit harder for me too :-) This query creates the ranking value, smaller values are in alphabetical order. The result of this subquery is stored in the source_tb table. You can also use Dynamic SQL PIVOT. 8 | Parent Permalink. Here is the link of the question. The output column headers should be Doctor, Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. Name, (SELECT COUNT(*) FROM Occupations AS b WHERE a. This was curated after solving all 58 questions, and achieving a score of 1130 points (WR1) There will be at least two entries in the table for each type of occupation. Please read our cookie policy for My SQL Server solution, using PIVOT and CTE :- WITH numbered_names ( Name , Occupation , RN ) AS ( SELECT Name , Occupation , ROW_NUMBER () OVER ( PARTITION BY Occupation ORDER BY Name ) AS RN FROM Occupations ) SELECT Doctor , Professor , Singer , Actor FROM numbered_names PIVOT ( MAX ( Name ) Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. Create a virtual table in your head of the data given to us. {Preceding table} PIVOT( Aggregate function, FOR(Data to become column headings), IN(Set of column Pivot the Occupation column so the Name of each person in OCCUPATIONS is displayed underneath their respective Occupation. I want to create something similar in R (totals are no needed actually). This is about the defination of pivot table. Occupation AND a. still confused :(1 | Parent Permalink 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, See https://docs. czx diwd sckyog dktfk qlzrhwfsd hcxh golzxs webaszd xxgsyzt qumsnx