Solved

# SQL Server Pivot

Posted on 2013-01-29
Medium Priority
300 Views
I have a database table where the data looks like this:

1                                       1                               1                 1                            1
2                                       1                                1                 2                               0
3                                       1                                2                 1                               0
4                                        1                               2                  2                              1

I need the output to look like this:

Wall      1      2
1      1      0
2      0      1

Wall = Occurence
1 = Multiplier Where Priority = 1
2 = Multiplier Where Priority = 2

I am getting this result back:

Wall      1      2
1      0      2
1      1      NULL
2      0      NULL
2      1      2

This is my query:

select Occurence AS Wall,Multiplier, [1], [2]
from (
select Occurence, Multiplier,Priority, row_number() over (partition by Occurence order by Occurence) rn
) o
pivot(max(Priority) for rn in ([1], [2])) p
ORDER BY Occurence

Thank you.
0
Question by:sherbug1015

LVL 25

Accepted Solution

chaau earned 2000 total points
ID: 38833438
This is what you are after:

``````select Occurence AS Wall, [1], [2]
from (
select Occurence, Multiplier, Priority
) o
pivot(MAX(Multiplier) for Priority in ([1], [2])) p
ORDER BY Occurence
``````

SQL Fiddle
0

Author Closing Comment

ID: 38835147
Thank you
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.