表只有2列的sql枢轴函数 [英] sql pivot function for a table with only 2 columns

问题描述

我正在尝试使用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等的第三列，因此我建议应用诸如

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小提琴.

现在，如果要使用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提琴.这些产生以下结果:

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

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

这篇关于表只有2列的sql枢轴函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！