SQL Server PIVOT returns only one row

All we need is an easy explanation of the problem, so here it is.

I have a simple table of of 2 columns(varchar):

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

I need to build a pivot table that would look something like this:

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

I have written this code to return build the pivot:

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

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

But I get only one row as a result:

Medic   |Actor  |Teacher    |Manager
Sandra  Marija  Virginija   Mia

Where did I made a mistake? I also can’t understand what aggregate function would be more applicable, since rows contain only text(varchar).

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

When you are using a PIVOT you are aggregating the data and since you are using MAX() you’re only going to be able to return one value for each of your new columns. In order to return multiple rows in each column, you need some sort of value to force SQL to return multiples. You can do this by using a windowing function like row_number().

Sample Data

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')
;

If you add row_number() and you query the following:

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

You’ll return the following data:

|     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 |
        

By adding the row_number you have another column that you are grouping by with the PIVOT function, so your final query will actually become:

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

Which will return the result that you want:

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply