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 |
|
raysefo
Constraint Violating Yak Guru
260 Posts |
 Posted - 2011-12-28 : 03:51:46
|
| Hi,I have a table with columns as follows:vendorid,itemid,monthlyconsumption,monthlydelivered,month,year1,56,100,50,3,20092,59,150,75,3,2009...500,1,200,150,12,2011As you can see from the sample data, there are monthly records for each year starting from 03/2009. I would like to implement a SELECT statement as follows:vendorid,itemid,monthlyconsumtion for 03/2009,monthly delivered for 03/2009,...,monthlyconsumption 12/2011,monthlydelivered 12/2011How can I do it?Best Regards. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
raysefo
Constraint Violating Yak Guru
260 Posts |
Posted - 2011-12-28 : 04:09:50
|
| Hi,Thanks for your quick reply. But I am not so into SQL so would you please explain your example?Best Regards. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-28 : 07:16:45
|
quote:
Originally posted by raysefo
Hi,Thanks for your quick reply. But I am not so into SQL so would you please explain your example?Best Regards.
simply put it will be like belowSELECT vendorid,itemid,MAX(CASE WHEN Rn=1 THEN monthlyconsumption END) AS monthlyconsumption1,MAX(CASE WHEN Rn=1 THEN monthlydelivered END) AS monthlydelivered1,MAX(CASE WHEN Rn=2 THEN monthlyconsumption END) AS monthlyconsumption2,...FROM(SELECT ROW_NUMBER() OVER (PARTITION BY vendorid,itemid ORDER BY month,year) AS Rn,*FROM table)tGROUP BY vendorid,itemid and for making it dynamic refer to link------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
How to implement such a SELECT?