SQL Help ! - SQL Server Forums

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)

SQL Help !

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2010-09-13 : 10:55:00

Folks:

I have the following data in a table (columns: Name, col1, col2, col3, col4, col5, col6). What I want is for a username if the number '1' is repeated continously 3 times in a column then to take a specific action (like update a record in some other table or email). Any help on the SQL is appreciated.

Name Col1 Col2 Col3 Col4 Col5 Col6

xyz 0 1 0 0 1 0
abc 0 0 1 1 1 0
pqr 0 0 0 0 0 1
mno 1 1 0 1 0 1
rst 0 1 1 1 0 0

In the above example the email should be sent to Names - abc, rst

Thanks !

CSears
Starting Member

39 Posts

Posted - 2010-09-13 : 11:13:54

Is there some way ordering this table and is it any column or one in particular?

sqldba20
Posting Yak Master

183 Posts

Posted - 2010-09-13 : 11:33:35

The ordering is based on userName and it can be any column.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-13 : 11:51:00

Maybe





DECLARE @foo TABLE (
	[name] VARCHAR(50)
	, [col1] BIT
	, [col2] BIT
	, [col3] BIT
	, [col4] BIT
	, [col5] BIT
	, [col6] BIT
	)

INSERT @foo
      SELECT 'xyz', 0, 1, 0, 0, 1, 0
UNION SELECT 'abc', 0, 0, 1, 1, 1, 0
UNION SELECT 'pqr', 0, 0, 0, 0, 0, 1
UNION SELECT 'mno', 1, 1, 0, 1, 0, 1
UNION SELECT 'rst', 0, 1, 1, 1, 0, 0

SELECT
	[name]
FROM
	@foo
WHERE
    CAST([col1] AS CHAR(1))
  + CAST([col2] AS CHAR(1))
  + CAST([col3] AS CHAR(1))
  + CAST([col4] AS CHAR(1))
  + CAST([col5] AS CHAR(1))
  + CAST([col6] AS CHAR(1))
    LIKE '%111%'

You won't be able to use any index with that though. (but indexes on bit fields are quite pointless)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources