Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    Unanswered: Using Data as Column Aliases

    Hi,

    I’m working with a really old design migrated to SQL 2005, in which I basically have two tables…

    Table 1 contains all the “proper” data, and has columns called: col_1, col_2, col_3

    Table 1’s data is something like:

    col_1, col_2, col_3
    Jack,jack@yahoo.ca,Toronto
    Jill,jill@hotmail.com,Montreal

    Table 2 contains meta-data for Table 1, specifically, it has two columns: column, meta-data

    Table 2’s data is something like:

    column,metadata
    col_1,name
    col_2,email
    col_3,city

    (Hopefully, my description of the design makes sense….basically; Table 2’s data describes what’s in each column of Table 1).

    So, the question, if I want to write a ‘SELECT’ on Table 1, how can I use the data in Table 2 as aliases (or column) headers.

    I’m currently going down the path of building dynamic SQL statements in T-SQL….but before I get too far, wanted to vet this idea here (it’s always been a fantastic resource for me)

    Thanks in advance!

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Look at PIVOT.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Look at redisigning
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2007
    Posts
    62
    Why? You must already know what the columns represent when you do the select statement. Just either
    a) Keep the current names and imply what they mean from the code
    b) Use an alias when you select (select col_1 as name, col_2 as email from table_1).
    Either way, your code has to know what to do with the data whether it has a proper name or not.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    why? because it is a asinine level misdirection probably left over from when 5 MB hard drives were all the rage.

    and it violates the KISS principle.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    okay, okay....

    yeah, I get it...

    redesign, stupid requirement....granted.

    I will continue on my assumption there's no way to do it.

    Thanks for the input guys.
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    PIVOT sounds like it might be your only hope; have you given that a shot yet?
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    LoztInSpace is correct. Unless there is more to this design than in your post then why not:
    Code:
    SELECT name = col1, email = col2
    FROM mytable
    ?? I.e. hard code the names.

    Of course renaming the columns in the table would be the ideal solution - this is not even really much of a redesign - you could create scripts to form all the DDL you will require. Why not create dynamic SQL as a one hit clean up rather than dynamic SQL to compensate for a silly initial design?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Bypass that nonsense and create VIEWs for each table that include the proper column names.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105
    unfortunalely, changing the design is not an option.

    here's the real kicker....I can't hardcode the aliases, because (you're not going to believe this) they can change.

    i.e col_1 one day could mean email, col_1 another day could mean city. (yes, there is actually a process currently which shifts data around based on the 'table 2' I mentioned earlier.

    It's all bad! ....and causes many a sleepless night.

    (I don't see how PIVOT can help....mainly, because I'm not PIVOTing or DEVPIVOTing this data.) ...although, thanks for showing me those functions.

    All of this stems from some guy's utopian idea (back in the early 90's) to not have any hard-coding in design (i.e. naming a column, table, etc)....all design is data-driven.
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - I suspected as much - I totally believe it. In that case a redesign is a big deal. This is not a million miles off an EAV design. Dynamic SQL is your only option.

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I would leave this sort of thing to the presentation layer. Cycle through the labels table to populate the headings, then cycle through the data to populate all the rest of the page. This may be more than you can get away with, though. Still, it makes a nice target.

  13. #13
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105
    That's basically what I've come up with as well...

    I'm doing a UNION....the first row of the query data will be the headers, and remaining rows will be the data. That's easy enough and I'll be able to sell it.

    thanks all!

Posting Permissions

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