Post reply

Use Values as Column Names

  • February 19, 2004 at 9:04 am

    #82004

    I'd like to do something like the following (except for many more fields):

    Table1: Id1 ,  DefId,  FixedField,  A1

    Table2: DefId ,  A1_Name

    Join Table1 to Table2 (via the DefId),  Show Values for Id1, DefId, FixedField, and A1  - BUT Show value of A1_Name as the column title of A1.

    For example where:  Id1 = 1, DefId = 1, FixedField = 4, A1 = 1234.44, and A1_Name: 'Engine Cost'.

    Show as the following:

     Id1:1 / DefId:1 / FixedField:4 / 'Engine Cost': 1234.44

    Any thoughts?

    Thanks,

    Allan

     

     

  • February 19, 2004 at 7:06 pm

    #494840

    Dynamic sql can help you.

    What about two RefID in table1, which one to pick up?

     

  • February 20, 2004 at 1:23 am

    #494871

    we have such "dynamic" tables.

    parametertable

      ( tablename, colparam, colname, startdatetime , enddatetime, whodidit)

    table

       (datetimeInsert, d001 float, d002 float, d003 varchar(500), ...)

    In the presentation layer, the table is accessed through a column-selection-panel and the query as well as the parameter-query is being prepared.

    This way, column-headings are split from the actual data. (2 queries)

    If one is trying to access the data directly using QA, he/she will have to perform a join or access the parametertable itself to find out which column he/she will need.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 20, 2004 at 4:56 am

    #494890

    I like alzdba's approach better.  If you used dynamic SQL to tried & implement this all in one query, then you'd need to grant SELECT rights on all the underlying tables to whomever might call this. 

  • February 20, 2004 at 9:00 am

    #494946

    I more or less decided that the best way to go would be to use 2 queries, one for the actual data, the other for the titles - and put them together in the presentation layer.  I was just wondering if using a view to merge them both together would be more elegant.

    As it turns out, the security concerns not withstanding, the best way to maintain the 'generic'-ness of the system would be to continue to refer to the fields by the A1(etc.) name, and to display the value of A1_Name as a label for the field.  (This way, I can point to the column A1 regardless of whether its name is 'Engine Cost' or 'Flypaper Weight' or 'Total Number of PCs').

    Thanks for your insights,

    Allan

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

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