Help with TSQL query.

Question

I'm using SQL-Server 2005 Standard.

I have Users table with following columns:

userID(int),
userImage(varchar),
userText(varchar),
userLastVisit(smalldatetime),
isActivated (bit),
userHobby1 (bit),
.....
userHobby10 (bit)
userCharacteristic1 (bit),
.....
userCharacteristic10 (bit)

I did 6 queries to select

1. users that haven't been on site for two weeks
2. users that doesn't have picture
3. users that doesn't have text
4. users that doesn't have a single hobby
5. users that doesn't have a single characteristic
6. users that did not activate their account

Now what i need to get is list of users with actions that need to be told about.

For example userID 2004 doesn't have an image and doesn't have a single hobby. Each single user should appear only once in the list with all it's actions need to be done.

Thanks in advance.

Answer 1

Try string concatenation and CASE WHEN:

SELECT UserID,
    CASE WHEN UserImage IS NULL THEN 'no image.' ELSE '' END +
    CASE WHEN UserText IS NULL THEN 'no text.' ELSE '' END +
    ... -- more CASE WHEN conditions
    AS Info
WHERE (UserImage IS NULL) 
   OR (UserText IS NULL)
   ... -- OR same conditions as in CASE WHEN clauses