Dynamic column naming.

  • Is it possible to name a column based on a field value. I want to name a column based on the current period number. So the current period is 8 I want the report to look like this.

    [font="Courier New"]

    PRODUCT DESC AUG JUL

    AAAAAAAA YYYYYY 500 150

    BBBBBBBBB YYYYYY 1500 2000

    ......

    ZZZZZZZZ YYYYYY 50 50[/font]

    Cols AUG & JUL are calculated based on the period number which is a look up on another table. AUG & JUL are summed calculations.

    My current report looks like this. I want to replace "TOTALP" with "AUG" and delete col "PERIOD" and replace "TOTALLP" with "JUL"

    [font="Courier New"]

    PRODUCT____DESC________________PERIOD TOTALP LAST PERIOD TOTALLP

    GR71080700 AAAAAAAAAAAAAAAAAAAAAUG1266JUL 28427

    FD60461900 BBBBBBBBBBBBBBBBBBBBAUG5744JUL 19866

    4004115000 CCCCCCCCCCCCCCCCCCCCAUG5916JUL 16380

    FL50080001 DDDDDDDDDDDDDDDDDDDDAUG2558JUL 11415

    PK22400300 EEEEEEEEEEEEEEEEEEEEAUG5400JUL 11100[/font]

  • Where are these results appearing - in SSMS, in an application, or in Reporting Services? This is the kind of thing that is best handled by the application (presentation) tier. If you don't have the option of changing the application, you can calculate the names of the columns and then use dynamic SQL to make those names appear at the top of the result set.

    John

  • I want a select statement I can use in a Excel.

  • I think I'd do it by running the query in SQL and then using an Excel macro to change the name of the column.

    John

  • I've sorted this by not returning the column names in Excel and hiding the cols with AUG & JUL in them and then on the cols with figures in I've set the column heading as a formula to equal row 2 of the months.

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

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