Solved

SQL Server Pivot

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to enforce inte 8 58
format nvarchar field as mm/dd/yyyy 4 78
SQL Syntax:  How to Find Commonality Among Similar Results 2 54
Sql Server group by 10 43
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
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…

809 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