Post reply

transpose columns to rows

  • 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

    week1 6

    week2 5

    week3 4

    week4 3

    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

  • November 19, 2014 at 1:36 pm

    #1759628

    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

    week1 6

    week2 5

    week3 4

    week4 3

    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/

  • December 3, 2014 at 9:02 am

    #1762428

    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?

  • December 3, 2014 at 10:53 pm

    #1762582

    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

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

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