x

Need help with using Pivot to get results (I think)

I've got a final table I've created which I believe I need to use PIVOT on in order to get my desired results, but not sure how to. Any help would be GREATLY appreciated as I'm new to most of this. (Using SQL Server 2005)

A representation of MyTable:

Person , date , C1 , C2

Person1 , 2009-01-01 , .89 , .39

Person1 , 2009-02-01 , .99 , .85

Person1 , 2009-03-01 , .78 , .98

etc..

Person2 , 2009-01-01 , .80 , .81

Person2 , 2009-02-01 , .75 , .79

etc..

A representation of the results I'd like to get (Each Person's percentages by month(date)):

Person , JanC1 , JanC2 , FebC1 , FebC2 , MarC1 , MarC2

Person1 , .89 , .39 , .99 , .85 , .78 , .98 , etc...

Person2 , .80 , .81 , .75 , .79 , etc...

...I initially tried to use Selects with Cases, but without setting Person to a specific one, I don't know how to generate results

more ▼

asked Nov 03, 2009 at 07:30 PM in Default

avatar image

aabeck
12 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I'm not sure if you can PIVOT on a date column, but this will work:

select            
    Person,            
    JanC1 = SUM(case when date = '2009-01-01' then C1 end),            
    JanC2 = SUM(case when date = '2009-01-01' then C2 end),            
    FebC1 = SUM(case when date = '2009-02-01' then C1 end),            
    FebC2 = SUM(case when date = '2009-02-01' then C2 end)            
from <YourTable>            
group by Person            

more ▼

answered Nov 03, 2009 at 09:33 PM

avatar image

Andomar
330 3 5 10

(comments are locked)
10|1200 characters needed characters left

It works very well - Thanks for your help & quick response Andomar! ...It looks like I made this much harder than it needed to be:)

more ▼

answered Nov 04, 2009 at 01:42 PM

avatar image

aabeck
12 1 1 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2030
x114

asked: Nov 03, 2009 at 07:30 PM

Seen: 1085 times

Last Updated: Nov 03, 2009 at 07:57 PM

Copyright 2017 Redgate Software. Privacy Policy