locked
need to generate column dynamically RRS feed

  • Question

  • Dear all.

    i have one report in which column is month like

    project     MAY-12  APR-12

    A             10           12

    B            20            25

    WHERE COLUMN IS DYNAMIC

    PLEASE SUGGSET

    REGARDS,

    VIPIN JHA

    • Moved by amber zhang Thursday, May 10, 2012 7:35 AM (From:Getting started with SQL Server)
    Wednesday, May 9, 2012 7:08 AM

Answers

All replies

  • See you other post; use the PIVOT function.

    http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/f164150e-b500-49aa-9362-75e6f29f4e1d


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Proposed as answer by Naomi N Thursday, May 10, 2012 5:09 PM
    Wednesday, May 9, 2012 7:13 AM
  • i got the solution for my first query.

    i want to know suppose i have a table having 3 column

    project ,month and score

    and i wnat to display the report

    project    may-2012  april-2012

    A            19                    20

    MEANS PROJECT A score 19mrks in may-2012 and 20mrks in apr-2012

    i want to list 12 month where lable is dynamic

    suppose in june it should come dynamically june-2012 in my above report

    regads,

    vipin jha

    Wednesday, May 9, 2012 7:20 AM
  • Hi Vipin Jha,

    Thank you for your update. I think you need to use dynamic pivot function.

    Please refer to these articles and follow the sample code here:

    1. Pivoting Data Using SQL 2005 (Part 2) http://jahaines.blogspot.com/2009/08/pivoting-data-using-sql-2005-part-2.html
    2. Pivoting Data Using SQL 2005 (Part 3) http://jahaines.blogspot.com/2009/08/pivoting-data-using-sql-2005-part-3.html
    3. Creating dynamic pivot reports on SQL Server http://weblogs.asp.net/gunnarpeipman/archive/2012/01/02/creating-dynamic-pivot-reports-on-sql-server.aspx

    Hope it is helpful.


    Regards, Amber zhang

    • Proposed as answer by Naomi N Thursday, May 10, 2012 5:10 PM
    • Marked as answer by Iric Wen Wednesday, May 16, 2012 7:32 AM
    Thursday, May 10, 2012 7:53 AM
  • Your table is dynamic or your columns will be dynamic???You said you have a table so I assume that the columns has to be dynamic. You can use dynamic sql as suggested by others or if you are just looking for a single year data , then it should be easy to list the month names (as it will be not more than 12).

    For a clear answer Please post the table structure+sample data as insert statement


    Thanks and regards, Rishabh K

    • Proposed as answer by Naomi N Thursday, May 10, 2012 5:10 PM
    • Marked as answer by Iric Wen Wednesday, May 16, 2012 7:32 AM
    Thursday, May 10, 2012 8:42 AM
  • I guess, it would be perfect if you leverage functionality of reporting systems to get result with dynamic column number.

    For instance, SQL Server Reporting Services have Tablix control for this.

    MS Excel have PivotTable.. 


    • Edited by Alex Volok Thursday, May 10, 2012 11:34 AM
    • Proposed as answer by Naomi N Thursday, May 10, 2012 5:10 PM
    • Marked as answer by Iric Wen Wednesday, May 16, 2012 7:32 AM
    Thursday, May 10, 2012 11:04 AM