sql pivot function for a table with only 2 columns

2019-01-29 08:56发布

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

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

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

I'd like to transform it to this:

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

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?

Thanks in advance!

1条回答
Viruses.
2楼-- · 2019-01-29 09:37

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.

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;

See SQL Fiddle with Demo.

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;

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

|   COMPANY | CATEGORY1 | CATEGORY2 |
|-----------|-----------|-----------|
| company 1 |    Arcade |    Action |
| company 2 |    Arcade | Adventure |
查看更多
登录 后发表回答