Solved

SQL Server Pivot

Posted on 2013-01-29
2
252 Views
Last Modified: 2013-01-30
I have a database table where the data looks like this:

CadenceDetailID      FKCadenceID      Priority      Occurence      Multiplier
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
      from tblCadenceStrategydetails_test
) o
pivot(max(Priority) for rn in ([1], [2])) p
ORDER BY Occurence

Could someone please help me with this.  I would be truly appreciative.

Thank you.
0
Comment
Question by:sherbug1015
2 Comments
 
LVL 24

Accepted Solution

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

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

Open in new window


SQL Fiddle
0
 

Author Closing Comment

by:sherbug1015
ID: 38835147
Thank you
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now