Post reply

Column name lookup

  • July 8, 2003 at 12:07 pm

    #357930

    I have two tables one is a lookup for the later which contains a column called 'columnname' this column contains the names of all of the columns in the second table. There is another column in the first table which holds a bit value indicating wheter the corresponding field is enabled or not. 2 Questions for the SQL Masters... First, how would I write a query that returns only the fields from the second table that are marked as enabled in my lookup table? Second, Is this bad design? Is there a better way to acomplish this?

  • July 8, 2003 at 12:20 pm

    #464565

    Using...

    Select * from information_schema.columns

    where table_name = 'my_table'

    ...should give you the column names of the table you want to query column names for. Then put a WHERE clause in your SELECT statement to display only enabled fields.

    HTH

    Billy

  • July 8, 2003 at 12:30 pm

    #464566

    I want to return the field values for the fields that are enabled not just the names of the feilds. Returning field names is the easy part 😉

  • July 9, 2003 at 1:46 pm

    #464732

    You might try something like this (I didn't actually run this in SQL so you might need to adjust it)

    DECLARE @selSQL NVARCHAR(2000), @colList VARCHAR(1500)

    SET @colList = ''

    SELECT @colList = @colList + CASE WHEN @colList = '' THEN '' ELSE ', ' END + columnname FROM Table2 WHERE enabled

    SET @selSQL = 'SELECT ' + @colList + ' FROM <SourceTable> WHERE <condition>'

    EXEC sp_executesql @setSQL

    It that close to what you are looking for?

    Guarddata-

  • July 9, 2003 at 4:07 pm

    #464754

    Ok, but what I need is to be able to join the result for each column with it's column name. So each row would have the column name and the column value. How could this be done?

  • July 9, 2003 at 4:30 pm

    #464756

    Oops - went horizontal when you wanted vertical

    DECLARE @colName VARCHAR(30)

    DECLARE enabledCol CURSOR FOR

    SELECT columnname from TABLE WHERE ENabled

    OPEN enabledCol

    FETCH NEXT FROM enabledCol INTO @colName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @selSQL = 'SELECT ''' + @colName + ''' ColName, ' + @colName + ' colValue FROM <sourcetable> where <condition>

    EXEC sp_executesql @selSQL

    FETCH NEXT FROM enabledCol INTO @colName

    END

    If you needed to, you could probably put this into a temporary table but you would need to convert the data (probably to a string) in the "colValue" column.

    Getting close?

    Guarddata-

  • July 9, 2003 at 5:29 pm

    #464758

    An alternative is, if the bit values do not change to 'often' use a view and a trigger that will redefine the view when a bit is flipped. Assumption: often implys once a day of less.

  • July 10, 2003 at 11:37 am

    #464945

    Thanks Guarddata,

    Any suggestions on how I could output this into one select statement. I need to return the result to the application level with one select statement...

  • July 10, 2003 at 1:24 pm

    #464966

    Just one result set? Sure.

    Create temporary table

    cursor

    insert into temptable

    exec sp_executesql @selSQL

    deallocate cursor

    select * from temp table

    (Hope that's not too sketchy)

    Guarddata-

Viewing 9 posts - 1 through 8 (of 8 total)

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