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 |
|
zhel04
Starting Member
38 Posts |
 Posted - 2011-02-15 : 05:40:12
|
| Hi! I'm a newbie when it comes to transposing Rows to Columns. Can someone help me with this? and the 24 months query. Please see below:The Table that i have:Date Qty Code Name01/01/2009 3 123 A02/01/2009 5 321 B03/01/2009 9 654 D.........12/01/2009 6 985 T01/01/2010 10 456 K02/01/2010 15 568 L.........12/01/2010 13 693 PExpected output must be:Name Code Qty1(Jan2009) Qty2(Feb2009)..... Qty13(Jan2010)... Qty24(Dec2010)Here's the recent code that i have but the problem is it can only get the 12 months so I've decided to come up with a stored procedure there will be an input from the interface. How will i add up to the query that the next month will be the 13th month.SELECT Code, Name,SUM(CASE WHEN Month(Date) = 1 THEN Qty ELSE 0 END) As Qty1, SUM(CASE WHEN Month(Date) = 2 THEN Qty ELSE 0 END) As Qty2,SUM(CASE WHEN Month(Date) = 3 THEN Qty ELSE 0 END) As Qty3,SUM(CASE WHEN Month(Date) = 4 THEN Qty ELSE 0 END) As Qty4, SUM(CASE WHEN Month(Date) = 5 THEN Qty ELSE 0 END) As Qty5,SUM(CASE WHEN Month(Date) = 6 THEN Qty ELSE 0 END) As Qty6,SUM(CASE WHEN Month(Date) = 7 THEN Qty ELSE 0 END) As Qty7, SUM(CASE WHEN Month(Date) = 8 THEN Qty ELSE 0 END) As Qty8,SUM(CASE WHEN Month(Date) = 9 THEN Qty ELSE 0 END) As Qty9,SUM(CASE WHEN Month(Date) = 10 THEN Qty ELSE 0 END) As Qty10, SUM(CASE WHEN Month(Date) = 11 THEN Qty ELSE 0 END) As Qty11,SUM(CASE WHEN Month(Date) = 12 THEN Qty ELSE 0 END) As Qty12FROM TblABC WHERE Month(date) BETWEEN '1' AND '12'Group By Code, NameHow will I do this in Stored Procedure?Hope can someone help me. I badly need help. :(Thanks. |
|
|
zhel04
Starting Member
38 Posts |
Posted - 2011-02-15 : 06:02:58
|
| Badly need help to all SQL Experts.. :( |
 |
|
|
Sachin.Nand
2937 Posts |
|
|
|
|
|
|
|
Stored Proc Transpose Data Rows to Data Columns