主页 / dba / 问题 / 294030
Accepted
Arturo Gonzalez
Asked: 2021-06-10 12:13:42 +0800 CST

SQL Server PIVOT 仅返回一行 [重复]

  • 772

我有一个简单的 2 列表（varchar）：

name    |occupation
___________________
Sandra  |Medic
Julija  |Actor
Marija  |Actor
Martyna |Manager
Aiste   |Teacher
Kristina|Teacher
Virg    |Teacher
Jurga   |Actor
Justina |Medic
Mia     |Manager

我需要构建一个看起来像这样的数据透视表：

Medic  | Actor | Teacher | Manager |
____________________________________
Justina| Julija| Aiste   | Martyna
Sandra | Marija| Kristina| Mia 
null   | Jurga | Virg    | null

我编写了这段代码来返回构建枢轴：

SELECT *
FROM (
  SELECT
    [name],
    [occupation]

  FROM employee
) as Results
PIVOT (
  max([name])
  FOR [occupation]
  IN (
    [Medic],
    [Actor],
    [Teacher],
    [Manager]
  )
) AS PivotTable

但结果我只得到一行：

Medic   |Actor  |Teacher    |Manager
Sandra  Marija  Virginija   Mia

我在哪里做错了？我也无法理解哪种聚合函数更适用，因为行仅包含文本（varchar）。

sql-server pivot

1 个回答

  1. Best Answer

    当您使用 a 时，PIVOT您正在聚合数据，并且由于您正在使用MAX()，您只能为每个新列返回一个值。为了在每列中返回多行，您需要某种值来强制 SQL 返回多行。您可以通过使用像row_number().

    样本数据

    if object_id('yourtable') is not null
    drop table Table1


CREATE TABLE yourtable
    ([name] varchar(8), [occupation] varchar(7))
;
    
INSERT INTO yourtable
    ([name], [occupation])
VALUES
    ('Sandra', 'Medic'),
    ('Julija', 'Actor'),
    ('Marija', 'Actor'),
    ('Martyna', 'Manager'),
    ('Aiste', 'Teacher'),
    ('Kristina', 'Teacher'),
    ('Virg', 'Teacher'),
    ('Jurga', 'Actor'),
    ('Justina', 'Medic'),
    ('Mia', 'Manager')
;

    如果您添加row_number()并查询以下内容：

    select name, occupation,
    rn = row_number() over(partition by occupation order by name) 
from yourtable

    您将返回以下数据：

    |     name | occupation | rn |
|----------|------------|----|
|   Julija |      Actor |  1 |
|    Jurga |      Actor |  2 |
|   Marija |      Actor |  3 |
|  Martyna |    Manager |  1 |
|      Mia |    Manager |  2 |
|  Justina |      Medic |  1 |
|   Sandra |      Medic |  2 |
|    Aiste |    Teacher |  1 |
| Kristina |    Teacher |  2 |
|     Virg |    Teacher |  3 |

    通过添加 row_number 您有另一列，您正在使用该PIVOT函数进行分组，因此您的最终查询实际上将变为：

    select Medic, Actor, Teacher, Manager
from 
(
    select name, occupation,
        rn = row_number() over(partition by occupation order by name) 
    from yourtable
) d
pivot
(
    max(name) 
    for occupation in (Medic, Actor, Teacher, Manager)
) pv

    这将返回您想要的结果：

    |   Medic |  Actor |  Teacher | Manager |
|---------|--------|----------|---------|
| Justina | Julija |    Aiste | Martyna |
|  Sandra |  Jurga | Kristina |     Mia |
|  (null) | Marija |     Virg |  (null) |
    • 3

相关问题