SQL 透视列值 [英] SQL pivot the column values

问题描述

样本输出

Jenny Ashley Meera Jane萨曼莎·克里斯汀·普里亚·朱莉娅NULL 凯蒂 NULL 玛丽亚

---

我的解决方案:

从中选择博士、教授、歌手、演员(select a.name, a.occupation, count(*) as row_num from Occupations a加入职业b在 a.occupation=b.occupation 和 (a.name>=b.name)按 a.name, a.occupation 分组按 row_num asc 排序；) 作为 t枢(最大值(名称)职业(博士、教授、歌手、演员))作为 pvt;

错误:

第 1 行的 ERROR 1064 (42000):您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 7 行 '' 附近使用的正确语法

第 7 行:

order by row_num asc;

我正在尝试学习高级 sql 概念，到目前为止我可以编写查询，但出现此错误.当我运行子查询时，它工作正常，我得到了一些输出，但是在使用 pivot 后我得到了这个错误.我不知道如何解决它以及导致此错误的原因.

我想通过 pivot 操作符而不使用 row_number 来完成.

子查询:

select a.name, a.occupation, count(*) as row_num from Occupations a..按 row_num 排序；

子查询输出:

贝尔维特教授 1简歌手 1詹妮弗 演员 1朱莉娅医生 1布兰妮教授 2priya 医生 2凯蒂 演员 2珍妮歌手 2玛丽亚教授 3萨曼莎 演员 3克里斯汀歌手 3米拉教授 4娜奥米教授 5朴雅卡教授 6

解决方案

可以使用窗口函数和条件聚合:

选择嗯，max(case whenoccupation = 'Doctor' then name end) 医生，max(case whenoccupation = 'Singer' then name end) 歌手，max(case whenoccupation = 'Actor' then name end) 演员从 (select t.*, row_number() over(按名称按职业顺序分区)rn来自 mytable t)按 rn 分组

子查询按姓名对具有相同职业的人进行排名.然后，您可以使用该信息生成行，并使用条件聚合访问每个职业的相应名称.

没有窗口函数，情况就不一样了.如果您的数据不是太大，一个选项是使用子查询模拟行号:

选择嗯，max(case whenoccupation = 'Doctor' then name end) 医生，max(case whenoccupation = 'Singer' then name end) 歌手，max(case whenoccupation = 'Actor' then name end) 演员从 (选择 t.*,(选择计数(*)从 mytable t1其中 t1.occupation = t.occupation 和 t1.name <= t.name) rn来自 mytable t)按 rn 分组

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, Singer, and Actor, respectively.

Note: Print NULL when there are no more names corresponding to an occupation.

Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

Sample Input

Sample Output

Jenny    Ashley     Meera  Jane
Samantha Christeen  Priya  Julia
NULL     Ketty      NULL   Maria

---

My solution:

select Doctor, Professor, Singer, Actor from
(
    select a.name, a.occupation, count(*) as row_num from Occupations a
    join Occupations b 
    on a.occupation=b.occupation and (a.name>=b.name)
    group by a.name, a.occupation
    order by row_num asc;
) as t
PIVOT
(
    max(name)
    for occupation in (Doctor, Professor, Singer, Actor)
)
as pvt;

Error:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7

Line no 7:

order by row_num asc;

I am trying to learn Advanced sql concepts so as far as now I am able to write the query but I am getting this error. When I run the subquery it works fine and I get some output but after using pivot I get this error. I don't know how to solve it and what's causing this error.

I want to do it by pivot operator and without using row_number.

Subquery:

select a.name, a.occupation, count(*) as row_num from Occupations a
.
.
order by row_num;

Subquery output:

Belvet Professor 1
Jane Singer 1
Jennifer Actor 1
Julia Doctor 1
Britney Professor 2
Priya Doctor 2
Ketty Actor 2
Jenny Singer 2
Maria Professor 3
Samantha Actor 3
Kristeen Singer 3
Meera Professor 4
Naomi Professor 5
Priyanka Professor 6

解决方案

You can use window functions and conditional aggregation:

select
    rn,
    max(case when occupation = 'Doctor' then name end) doctor,
    max(case when occupation = 'Singer' then name end) singer,
    max(case when occupation = 'Actor'  then name end) actor
from (
    select t.*, row_number() over(partition by occupation order by name) rn
    from mytable t
)
group by rn

The subquery ranks persons having the same occupatin by name. You can then use that information to generate the rows, and access the corresponding name for each occupation with a conditional aggregate.

Without window functions, it is different. If your data is not too large, one option emulates row number with a subquery:

select
    rn,
    max(case when occupation = 'Doctor' then name end) doctor,
    max(case when occupation = 'Singer' then name end) singer,
    max(case when occupation = 'Actor'  then name end) actor
from (
    select t.*, 
        (
            select count(*) 
            from mytable t1 
            where t1.occupation = t.occupation and t1.name <= t.name
        ) rn
    from mytable t
)
group by rn

这篇关于SQL 透视列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！