iterate through many columns to search for a value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

iterate through many columns to search for a value

Author

Topic

jayram
Starting Member

47 Posts

Posted - 2014-04-24 : 11:55:12

i have table like below

CREATE TABLE [dbo].[SAMPLE](
[COL_01] [nvarchar](50) NULL,
[COL_02] [nvarchar](50) NULL,
[COL_03] [nvarchar](50) NULL,
[COL_04] [nvarchar](50) NULL,
[COL_05] [nvarchar](50) NULL,
[COL_06] [nvarchar](50) NULL,
[COL_07] [nvarchar](50) NULL,
[COL_08] [nvarchar](50) NULL,
[COL_09] [nvarchar](50) NULL,
[COL_10] [nvarchar](50) NULL,
[COL_11] [nvarchar](50) NULL,
[COL_12] [nvarchar](50) NULL,
[COL_13] [nvarchar](50) NULL,
[COL_14] [nvarchar](50) NULL,
[COL_15] [nvarchar](50) NULL,
[COL_16] [nvarchar](50) NULL,
[COL_17] [nvarchar](50) NULL,
[COL_18] [nvarchar](50) NULL
) ON [PRIMARY]

i want to search for a value like 'Unknown' in each of these columns. how do iterate through these 18 columns to find a value 'Unknown'

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2014-04-24 : 13:14:50

1. SELECT * FROM SAMPLE
WHERE [COL_01] = 'Unknown' OR
[COL_02] = 'Unknown' OR
[COL_03] = 'Unknown' OR
[COL_04] = 'Unknown' OR
[COL_05] = 'Unknown' OR
[COL_06] = 'Unknown' OR
[COL_07] = 'Unknown' OR
[COL_08] = 'Unknown' OR
[COL_09] = 'Unknown' OR
[COL_10] = 'Unknown' OR
[COL_11] = 'Unknown' OR
[COL_12] = 'Unknown' OR
[COL_13] = 'Unknown' OR
[COL_14] = 'Unknown' OR
[COL_15] = 'Unknown' OR
[COL_16] = 'Unknown' OR
[COL_17] = 'Unknown' OR
[COL_18] = 'Unknown'

2. Don't design your table that way. Having multiple columns of the same type of data is a poor design practice and should be avoided, as the above query demonstrates. Use something like this:

CREATE TABLE Sample(TermNumber int not null, SearchTeam nvarchar(50) not null)

INSERT Sample VALUES(1,'Unknown')
INSERT Sample VALUES(1,'Something')
INSERT Sample VALUES(2,'Something Else')

SELECT * FROM Sample WHERE SearchTerm='Unknown'

This query will never need to change and you can expand beyond the 18 search columns you currently have without modifying the table structure.

jayram
Starting Member

47 Posts

Posted - 2014-04-24 : 13:34:30

Thanks but the data table comes from a client and i currently use something like this to generate a SQL

SELECT 'Select * from SAMPLE where '+ c.name + ' = ''Unknown'''
FROM sysobjects o
INNER JOIN syscolumns c ON c.id = o.id
INNER JOIN systypes t ON t.xusertype = c.xusertype
WHERE o.name = 'SAMPLE'
AND C.name like ( 'COL_%' )

But i was wondering if it could be wrapped in a cursor or stored procedure.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2014-04-24 : 18:00:05

You could use a cursor but it will likely be slower. Are you saying the client's data has a dynamic number of columns?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources