asked
transpose columns to rows dynamically
need to display the most recent 6 months' data like this (see the attached). Basically, I need to transpose the most recent 6 months' data to rows. But I don't know ahead of time the names of the month because this will be automated. How do I accomplish this. Thanks [link text][1] [1]: /storage/temp/1078-report.txt
Hello, For Transposing, I think you can use PIVOT and UNPIVOT features of SQL Server. Hope [these links][1] help! [1]:
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
--Here is the code to generate a test table with data. Couldn't see the code I posted. CREATE TABLE [dbo].[t]( [a] [float] NULL, [b] [float] NULL, [c] [float] NULL, [d] [float] NULL, [e] [float] NULL, [year] [float] NULL, [month] [float] NULL, [monName] [nvarchar](3) NULL ) ON [PRIMARY] insert into t (a, b,c,d,e,year,month,monName) values (80.11, 2.11, 0.8801, 0.8611,180,2013,1,'Jan'), (80.21, 2.21, 0.8802, 0.8612,181,2013,2,'Feb'), (80.22, 2.31, 0.8803, 0.8613,182,2013,3,'Mar'), (80.23, 2.41, 0.8804, 0.8614,183,2013,4,'Apr'), (80.24, 2.51, 0.8805, 0.8615,184,2013,5,'May'), (80.25, 2.61, 0.8806, 0.8616,185,2013,6,'Jun'), (80.26, 2.61, 0.8806, 0.8616,185,2013,7,'Jul'), (80.27, 2.61, 0.8806, 0.8616,185,2013,8,'Aug'), (80.28, 2.61, 0.8806, 0.8616,185,2013,9,'Sep'), (80.02, 2.01, 0.8790, 0.8609,179,2012,9,'Sep'), (80.01, 2.00, 0.8791, 0.8608,178,2012,8,'Aug'), (80.03, 2.00, 0.8791, 0.8608,178,2012,10,'Oct'), (80.04, 2.00, 0.8791, 0.8608,178,2012,11,'Nov'), (80.05, 2.00, 0.8791, 0.8608,178,2012,12,'Dec')
The site works on voting. Every helpful answer can get a vote by clicking on the thumbs up next to those answers. If any one answer lead to a solution, click on the check mark next to that answer.
Also, here's a nice article on using PIVOT to create reports dynamically:
https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/
Thank you both for your answers. I used matrix table to resolve the issue in SSRS. But the info you provided will be useful for future reference.
If you solved your own problem, then you can click the check mark next to this answer to mark it as the final solution.
17 People are following this question.
