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!
Since you need a third column that contains
category1
,category2
, etc, then I would suggest applying a windowing function likerow_number()
to your data first before attempting to convert the data into columns. Therow_number()
function will create a unique sequenced number for eachcompany
andcategory
, 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()
: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:See SQL Fiddle with Demo. These generate a result of: