Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
glendcruz
Yak Posting Veteran
60 Posts |
 Posted - 2010-11-02 : 19:32:57
|
| This is one of the Queries I seen on this forums a few days agoDECLARE @V_BillingEntriesFull TABLE ( ArrivalDate datetime, TotalAmount money )insert into @V_BillingEntriesFullselect '2009/01/01', 500 union allselect '2010/02/01', 600 union allselect '2011/03/01', 750 union allselect '2009/01/01', 600 union allselect '2010/02/01', 550 union allselect '2011/03/01', 750 union allselect '2009/01/01', 4200 union allselect '2010/02/01', 100 union allselect '2011/03/01', 2140 The client wanted the output asMonth 2009 2010 ..... -----------Jan 500Feb 250Mar 4200Some one sujested to try using a Pivot. I tried doing it myself but i just cannot get itI am sure there are a few exceptional Gurus who can work it out.Will be greately appreciated if any one can do it.Thanks to all |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-11-02 : 22:14:15
|
[code]select *from ( select yr = datepart(year, ArrivalDate), mth = datename(month, ArrivalDate), TotalAmount from @V_BillingEntriesFull ) d pivot ( sum(TotalAmount) for yr in ([2009], [2010], [2011]) ) p[/code]you will need to use Dynamic SQL if the number of year is not fixed and variables
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
glendcruz
Yak Posting Veteran
60 Posts |
|
|
glendcruz
Yak Posting Veteran
60 Posts |
|
|
|
|
|
|
|
Pivot