Professional Web Applications Themes

A better way to hold column titles in separate table? - Microsoft SQL / MS SQL Server

Hi all, whats the best way to hold columns titles? I have coded (below) a solution but my guess is this is not the best mothod! Example - Table 1 holds values, Table 2 holds the titles for Table 1 columns... Table1 C1, C2, C3 7, 12, 15 Table2 C1, C2, C3 'Title for C1', 'Title for C2', Title for C3' CREATE PROCEDURE titled AS declare t1 char(10) declare t2 char(10) declare t3 char(10) declare sql varchar(200) select top 1 t1=t1, t2=t2, t3=t3 from table2 --<<< this works set sql='SELECT C1 as [' + t1 + '],C2 as [' + ...

  1. #1

    Default A better way to hold column titles in separate table?

    Hi all,
    whats the best way to hold columns titles? I have coded (below) a
    solution but my guess is this is not the best mothod!

    Example - Table 1 holds values, Table 2 holds the titles for Table 1
    columns...

    Table1
    C1, C2, C3
    7, 12, 15

    Table2
    C1, C2, C3
    'Title for C1', 'Title for C2', Title for C3'

    CREATE PROCEDURE titled AS
    declare t1 char(10)
    declare t2 char(10)
    declare t3 char(10)
    declare sql varchar(200)
    select top 1 t1=t1, t2=t2, t3=t3 from table2 --<<< this works
    set sql='SELECT C1 as [' + t1 + '],C2 as [' + t2 + '], C3 as [' + t3 +
    '] FROM Table1'
    exec(sql)
    GO

    Theres got to be a better way, right?

    PS, before any one asks why, we have generic tables spread accross many
    nodes that on occation differ slighlty, the titles change quite often and
    we'd rather not code querys / views for exception cases.


    Stevie_mac Guest

  2. #2

    Default Re: A better way to hold column titles in separate table?

    > we'd rather not code querys / views for exception cases.
    Eh? But in your system you have to add a column to Table2 for each column
    change. How can it be easier to add a column to a table than to a view?

    Why not create a table listing all your titles and use this to generate
    dynamic SQL to create the views automatcially. This could be a once-only DBA
    task each time you publish revisions to the DB. But I still don't see the
    benefit of this over giving the columns proper names in the base tables.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Stevie_mac" <com> wrote in message
    news:bf0k7d$rnv$svr.pol.co.uk... 


    David Guest

  3. #3

    Default Re: A better way to hold column titles in separate table?

    Perhaps i was not as clear as i intended!
    the 'Titles table' would be configurable from an editor (say a web form) by
    non technical users, the number of columns will remain the same - so once
    the SPROC/View was coded, no changes to SQL views/querys would be required.
    Is that clearer? or am i missing your point?

    PS, the reason the titles change is that the source data that enters these
    columns can be changed by other staff to suit there needs - this may change
    every week as they around setting up their source data + definitions
    (usually into columns that are marked Spare). We have little control over
    the source but do have absolute control over the presentation & are therfore
    required to update titles/definitions as required. If we could set up a
    user interface then that would make the whole world a lot easier! [i think
    :)]

    "David Portas" <org> wrote in message
    news:phx.gbl... 
    > Eh? But in your system you have to add a column to Table2 for each column
    > change. How can it be easier to add a column to a table than to a view?
    >
    > Why not create a table listing all your titles and use this to generate
    > dynamic SQL to create the views automatcially. This could be a once-only[/ref]
    DBA [/ref]
    + [/ref]
    and 
    >
    >[/ref]


    Stevie_mac Guest

  4. #4

    Default Re: A better way to hold column titles in separate table?

    why don't you go for a titles-table to be accessed dependant of the user and
    NOT involved in the query for the actual data ?
    Seperate presentation from actual data.

    jobi

    "Stevie_mac" <com> wrote in message
    news:bf0mf8$ri9$svr.pol.co.uk... 
    by 
    required. 
    change 
    therfore 
    > > Eh? But in your system you have to add a column to Table2 for each[/ref][/ref]
    column 
    > DBA [/ref]
    the [/ref][/ref]
    t3 [/ref][/ref]
    many [/ref]
    > and 
    > >
    > >[/ref]
    >
    >[/ref]


    jobi Guest

Similar Threads

  1. Replies: 5
    Last Post: March 20th, 08:10 PM
  2. Cfchart column titles are mispositioned
    By Nafiganado in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: June 23rd, 12:30 PM
  3. add column to table with sql
    By cf_chick in forum Coldfusion Database Access
    Replies: 3
    Last Post: May 25th, 06:49 PM
  4. Table -- Floating Column Bar
    By coryd218 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: May 18th, 07:07 PM
  5. How do I create a YES/NO column in my table?
    By michaaal in forum ASP Database
    Replies: 5
    Last Post: July 5th, 09:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139