![]() This may seem like it’s the end, if you run just your case statement alone you’ll get the following: SELECTĬASE WHEN occupation = 'Actor' THEN name END AS 'Actor' You can THEN, pull in the relevant values based on the aforementioned criteria set. CASE allows you to basically build new columns based on a condition. ![]() Basically, when a row value equals a particular occupation – then the name should show up. Here’s where the pivoting is actually done. CASE WHEN occupation = 'Doctor' THEN name END AS 'Doctor', The reason you do this is so that you don’t need to nest the MAX functions within your case statement (it gets really messy) and you’ll need a GROUP BY in your nested statement which messes everything up. I created a VIEW (because I prefer views), but you could have made a procedure or function all the same. SELECT MAX(Doctor),MAX(Professor),MAX(Singer),MAX(Actor) FROM pq ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) as cr My Query CREATE VIEW pq AS (ĬASE WHEN occupation = 'Doctor' THEN name END AS 'Doctor',ĬASE WHEN occupation = 'Professor' THEN name END AS 'Professor',ĬASE WHEN occupation = 'Singer' THEN name END AS 'Singer',ĬASE WHEN occupation = 'Actor' THEN name END AS 'Actor', This is extremely important to note, because you can only have NULLs for the maximum amount of the highest occupation category (in this case it’s Professor). The Solutionįirstly, this is the solution output that you want: Aamina Ashley Christeen Eve The empty cell data for columns with less than the maximum number of names per occupation (in this case, the Professor and Actor columns) are filled with NULL values. The fourth column is an alphabetically ordered list of Actor names. The third column is an alphabetically ordered list of Singer names. The second column is an alphabetically ordered list of Professor names. The first column is an alphabetically ordered list of Doctor names. The OCCUPATIONS table is described as follows: Note: Print NULL when there are no more names corresponding to an occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively. Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |