Solved

# SQL Server Pivot

Posted on 2013-01-29
252 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 24

Accepted Solution

chaau earned 500 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

### Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.