We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Advanced Select
  4. Occupations
  5. Discussions

Occupations

Sort by

recency

|

2011 Discussions

|

  • wangyx28
     + 0 comments

    此处max实际上是在筛选非null项，所以换成min效果也是一样的，后文中group by rownum，产生了二维的数据，但实际上只有对角线上有非null值，此处是为了降维 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() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNum FROM OCCUPATIONS ) AS Sub GROUP BY RowNum ORDER BY RowNum;

  • sravanti_ku
     + 0 comments

    Please can someone make me understand why we are using partition here when we have already used Case statements. Thanks in advance.

  • 5colours_himans1
     + 0 comments

    select Doctor, Professor,Singer,Actor from (select occupation,name, dense_rank() over(partition by occupation order by name ) as rnk from occupations) pivot (max(name) for occupation in ('Doctor' as Doctor,'Professor' as Professor ,'Singer' as Singer ,'Actor' as Actor )) order by rnk ;

  • anhpd_soict_hust
     + 0 comments
    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() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNum
    FROM OCCUPATIONS
) AS NumberedOccupations
GROUP BY RowNum
ORDER BY RowNum;

  • thanhmantm1999
     + 0 comments

    with PivotTable as ( select Name, occupation, row_number() over(PARTITION BY occupation order by name) as RowNum 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 Name end) as Singer, Max(Case when occupation ='Actor' then Name end) as Actor from PivotTable group by RowNum