Solved

SQL Server Pivot

Posted on 2013-01-29
2
256 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

932 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

13 Experts available now in Live!

Get 1:1 Help Now