sql - 数据透视表将行转换为列

Question

姓名	职业
萨曼莎	医生
朱莉娅	演员
玛丽亚	演员
米拉	歌手
阿什利	教授
凯蒂	教授
克里斯汀	教授
简	演员
珍妮	医生
普里亚	歌手

嗨，我有一个如上所述的餐桌 职业

我希望输出像

医生	教授	歌手	演员
珍妮	阿什利	米拉	简
萨曼莎	克里斯汀	普里亚	朱莉娅
无效的	凯蒂	无效的	玛丽亚

我在下面尝试了这个 SELECT 语句，但它没有按预期工作：

SELECT rank,
       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
  (     
    SELECT o1.Occupation,
           o1.name,
           (SELECT COUNT(*) 
              FROM Occupations o2 
             WHERE o1.Occupation=o2.Occupation 
               AND o1.name > o2.name ) rank       
      FROM Occupations o1)
 GROUP BY rank  

标签： sqloraclepivot

Answer 1

似乎您需要按name列对结果进行排序以及分析功能，例如ROW_NUMBER()假设您的数据库版本至少为9i，例如

SELECT rank,
       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 Occupation, name,
               ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name)-1 rank       
          FROM Occupations
       )
 GROUP BY rank  
 ORDER BY rank

用ELSE NULL例是多余的。

或者

您也可以使用PIVOT子句

SELECT *
  FROM (     
        SELECT occupation, name,
               ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name)-1 rank       
          FROM Occupations
       )
 PIVOT (
        MAX(name) FOR occupation IN ('Doctor'    AS "Doctor",
                                     'Professor' AS "Professor",
                                     'Singer'    AS "Singer",
                                     'Actor'     AS "Actor")
       )     
 ORDER BY rank   

Demo