question

whly avatar image
whly 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
sql-server-2008dynamic
report.txt (493 B)
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
10 Likes 10 ·
--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')
0 Likes 0 ·
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.
0 Likes 0 ·
tomgough79 avatar image
tomgough79 answered
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/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

whly avatar image
whly answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If you solved your own problem, then you can click the check mark next to this answer to mark it as the final solution.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.