只有 2 列的表的 sql 数据透视函数

2022-01-22 00:00:00 pivot sql-server

我正在尝试使用 SQL Server 中的数据透视函数来转换一些结果，但遇到了麻烦.

I'm trying to use the pivot function in SQL Server to transform some results, but I'm running into trouble.

该表只有 2 列，如下所示:

The table only has 2 columns, which look like this:

company      category
-----        -----
company 1    Arcade 
company 1    Action 
company 2    Arcade 
company 2    Adventure

我想把它改成这样:

company      category 1     category 2
-----        -----          -----
company 1    Arcade         Action    
company 2    Arcade         Adventure

到目前为止，我能找到的只是枢轴函数的示例，其中原始结果中的第 3 列带有类别 1"或类别 2"，然后使用这些列中的值作为新的名称，旋转的列.

So far all I can find are examples of pivot functions where there is a 3rd column in the original results with "category 1" or "category 2", which then uses the values in those column as the names of the new, pivoted columns.

我想做的只是从头开始定义列的名称.有没有办法用枢轴功能做到这一点?

What I want to do is simply define the names of the columns from scratch. Is there a way to do this with the pivot function?

提前致谢！

推荐答案

既然你需要第三列包含category1、category2等，那么我建议申请像 row_number() 这样的窗口函数在尝试将数据转换为列之前，请先查看您的数据.row_number() 函数将为每个 company 和 category 创建一个唯一的序列号，然后您将使用此计算值对数据进行透视.

Since you need a third column that contains category1, category2, etc, then I would suggest applying a windowing function like row_number() to your data first before attempting to convert the data into columns. The row_number() function will create a unique sequenced number for each company and category, you will then use this calculated value to pivot the data.

转换数据的最简单方法是使用聚合函数和 CASE 表达式.首先，您将使用子查询来生成 row_number():

The easiest way to convert the data would be to use an aggregate function along with a CASE expression. First, you will use a subquery to generate the row_number():

select company,
  max(case when seq = 1 then category end) Category1,
  max(case when seq = 2 then category end) Category2
from
(
  select company, category,
    row_number() over(partition by company
                              order by company) seq
  from yourtable
) d
group by company;

参见 SQL Fiddle with Demo.

现在，如果您想使用 PIVOT 函数，您仍将使用 row_number()，但您会将新计算的序列作为新列名:

Now, if you want to use the PIVOT function you would still use the row_number(), but you would place the new calculated sequence as the new column names:

select company, category1, category2
from
(
  select company, category,
    'category'+
      cast(row_number() over(partition by company
                              order by company) as varchar(10)) seq
  from yourtable
) d
pivot
(
  max(category)
  for seq in (Category1, Category2)
) piv;

参见 SQL Fiddle with Demo.这些会产生以下结果:

See SQL Fiddle with Demo. These generate a result of:

|   COMPANY | CATEGORY1 | CATEGORY2 |
|-----------|-----------|-----------|
| company 1 |    Arcade |    Action |
| company 2 |    Arcade | Adventure |

相关文章