transpose columns to rows

Question

transpose columns to rows

shree_z

SSC-Addicted

Points: 464
More actions
November 19, 2014 at 1:33 pm

#292201

I have a table with week 1 to week 52 columns that holds data for no: of days worked in each week per employee.
In SSRS I want these week1 week2 fields appear as rows with no:of days worked against these rows.
no:of days worked
week16
week25
week34
week43
I want to further group these weeks for each month also for a year, but Initially I would like to know how to achieve above results.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

shree_z (11/19/2014)
I have a table with week 1 to week 52 columns that holds data for no: of days worked in each week per employee.
In SSRS I want these week1 week2 fields appear as rows with no:of days worked against these rows.
no:of days worked
week16
week25
week34
week43
I want to further group these weeks for each month also for a year, but Initially I would like to know how to achieve above results.
Thanks

Looks like a basic UNPIVOT to me. http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

shree_z SSC-Addicted Points: 464 More actions · Answer 2

Thanks for the reply.

Now I have a table with columns like

start of week work hours

1/5/2014 4

1/12/2014 5

1/19/2014 3

....

12/21/2014 1

there are 52 rows for 52 weeks.

In report builder, I want to group these rows monthly and have the data appear like below

Jan 2014 Feb 2014 March.... Dec

week work hours week work hours

1/5/2014 4 2/2/2014 3

1/12/2014 5 2/9/2014 2

1/19/2014 3 2/16/2014 4

1/26/2014 1 2/23/2014 6

How to accomplish this result?

pietlinden SSC Guru Points: 63503 More actions · Answer 3

I answered this already here:

For a minute I thought my post disappeared... then I realized you were double-posting. While I realize that you may feel that double-posting gets you more attention, it really doesn't. It fragments the responses, because someone may have answered your post elsewhere.

Thanks,

Pieter