Find a column

Question

Post reply

Find a column

Doug Simmons

Ten Centuries

Points: 1373
More actions
March 31, 2005 at 9:53 pm

#92791

My table has a row for each day through the next 3 years.
It has about 50 columns.
Each cell contains an int.
Is there a way to write a SELECT statement that will return to me the names of any columns that contain the int I specify in the statement, (on a particular date)?
Doug

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

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

jordanac Hall of Fame Points: 3653 More actions · Answer 1

--Get the object id

declare @Object_id int

select @Object_id = object_id ('table')

--Get the column names

SELECT [name] as coll_name

FROM syscolumns

WHERE (id = @Object_id)

--Put the column names into a data set and scroll through it

--I was too lazy and put the data into a cursor. I did not post the curser as I --will get too many anti cursor comments---hope this helps a little bit

Andy.

wz700 SSCertifiable Points: 7133 More actions · Answer 2

select 'col1name' where exists(select * from tbname where col1name = intvalue)

union

select 'col2name' where exists(select * from tbname where col2name = intvalue)

..

Doug Simmons Ten Centuries Points: 1373 More actions · Answer 3

An additional difficulty that I should have mentioned is that I don't know the names of the columns - they are numbers assigned by the user, but they could be any smallint.

Doug

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 4

Doug,

You could use system metadata to construct your query (with syscolumns or the equivalent information_schema view), and then use the undocumented xp_execresultset to execute the query itself.

xp_execresultset takes two arguments

1. a select statement that produces a result set to execute

2. database name to execute it in (here, tempdb)

Here's an example, a bit kludged, and one that should be cleaned up with dynamic table names, etc for real-life. This is meant more as a one-off, ad hoc query rather than a production code solution.

use tempdb
-- Sample Table and Data
if object_id('dbo.MyTable') is not null
    drop table dbo.MyTable
create table MyTable
   (RowDate    datetime   not null
   ,Col1       int
   ,Col2       int
   ,Col3       int
   )
insert dbo.MyTable
select '4/1/2005', 1,2,3
UNION
select '4/2/2005', 1,5,6
UNION
select '4/3/2005', 1,2,7
UNION
select '4/4/2005', 1,8,8
go
-- query of all columns, all dates where Int Value = 2
-- Note the hardcoded 2 indicated below
if object_id('tempdb.dbo.#holding') is not null
   drop table #holding
create table #holding
   (RowDate datetime, ColName sysname)
exec master.dbo.xp_execresultset N'

   select ''insert #holding select distinct RowDate, '''''' + COLUMN_NAME + '''''' as ColName
                  from dbo.MyTable
                  where '' + COLUMN_NAME + '' = 2    ---- HARDCODED 2
                 ''
   from    information_schema.columns
   where   table_name = ''MyTable''
      and data_type = ''int'' ', N'tempdb'
select * from #holding
go

-- query of all columns where Int Value = 8 and Date = 4/4/2005
-- Note the hardcoded values for 8 and the date
if object_id('tempdb.dbo.#holding') is not null
   drop table #holding
create table #holding
   (RowDate datetime, ColName sysname)
exec master.dbo.xp_execresultset N'

   select ''insert #holding select distinct RowDate, '''''' + COLUMN_NAME + '''''' as ColName
                  from dbo.MyTable
                  where '' + COLUMN_NAME + '' = 8    ---- HARDCODED 8
                    and RowDate = ''''4/4/2005''''    ---- HARDCODED Date
                 ''
   from    information_schema.columns
   where   table_name = ''MyTable''
      and data_type = ''int'' ', N'tempdb'
select * from #holding

Does this work for your needs?

Scott Thornburg

Doug Simmons Ten Centuries Points: 1373 More actions · Answer 5

Thanks so much Scott. I'm sure this will do once I've plugged in all the variables.

I am surprised though, that what is so easy one way round - find the value in a column - is so difficult the other way round - find a column or columns containing a particular value - (given that you know which row or rows to look in).

But thanks again so much. I would never have worked that out for myself!

Doug

wz700 SSCertifiable Points: 7133 More actions · Answer 6

One can get column name from systable. Use sql to generate sql statement, then run the generated statement.

Example; find which varchar fields in authors (pubs database) has value of 'White'

select 'select ''' + name + ''' where exists(select * from authors where ' + name + ' =''White'')' + char(13) + 'union'

from syscolumns where id = object_id('authors') and xtype = 167

paste the result (ignore the last union) and run it.

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 7

Doug,

You're welcome. I must admit that for a one-off situation, I'm more likely to use a sollution like wz700 provided -- use SQL to create SQL, then cut and paste to the execution window and run. Effectively, that's what xp_execresultset does in a single step, but it's a bit messier because you have to make sure you handle all those quotes correctly.

Also, for large tables, wz700's solution with the exists will definitely return faster. For a table with ~1000 rows for 3 years of dates, it may not be very noticeable.

Glad to help,

Scott Thornburg

Doug Simmons Ten Centuries Points: 1373 More actions · Answer 8

Thanks to wz700. I now understand exactly how to do what I need.

I still think though, that there is a gaping hole in SQL that there isn't a simple SQL statement of the type:

WHERE columnname VALUE = nnnn;

Doug