XML Feeds
If you want to show a 1 when there is a value for something in the column and 0 if none of the rows have that values you typically do something like this
- CASE WHEN SUM(CONVERT(INT,SomeValue)) > 0 THEN 1 ELSE 0 END
Basically you sum it up and if the sum is greater than 0 then you show 1 otherwise you show 0
Here is what it might look like in code
First create the following table with this data
- CREATE TABLE #Cars(id INT,brand VARCHAR(20),HasDefects BIT)
- INSERT #Cars VALUES(1,'Chevy Corvette',1)
- INSERT #Cars VALUES(2,'Ford Taurus',0)
- INSERT #Cars VALUES(3,'Ford Taurus',1)
- INSERT #Cars VALUES(4,'BMW 635 CSi',0)
- INSERT #Cars VALUES(5,'BMW 635 CSi',0)
- INSERT #Cars VALUES(6,'Fiat 500',1)
- INSERT #Cars VALUES(7,'Fiat 500',1)
And here is our CASE WHEN THEN ELSE query
- SELECT brand,CASE WHEN SUM(CONVERT(INT,HasDefects)) > 0 THEN 1 ELSE 0 END AS HasDefects
- FROM #Cars
- GROUP BY brand
We need to convert HasDefects to an integer before using the sum function, otherwise you will get the following error
Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a bit data type as an argument.
So how can we change that to use the sign function? It is very easy all you have to do is wrap the sign function around the sign function
- SELECT brand, SIGN(SUM(CONVERT(INT,HasDefects))) AS HasDefects
- FROM #Cars
- GROUP BY brand
Here is our output
--------------------
brand HasDefects BMW 635 CSi 0 Chevy Corvette 1 Fiat 500 1 Ford Taurus 1
As you can see that made the code smaller by about 20 bytes. This of course will only work if you want 0 and 1; if you want more posibilities then you need to use case. Another reason why you maybe don't want to use the sign function is that someone looking at your code might not immediately know what the code is supposed to do.
Remember you should always write code with the assumption that the person who will be maintaining your code is a psychopatic killer who has your address ;-)
How does the sign function work?
If the value is negative then -1 is returned
If the value is positive then 1 is returned
If the value is 0 then 0 is returned
If the value is NULL then NULL is returned
Scale will affect the output also; if you use -22.0001 then -1.0000 will be returned and if you use -22.01 then -1.00 will be returned
Here is a query you can run to see what sign returns for different values
- SELECT SIGN (0), -- 0
- SIGN (1), -- 1
- SIGN (-1), -- -1
- SIGN (null), --null
- SIGN (-200), -- -1
- SIGN (200), -- 1
- SIGN (-22.0001), -- -1.0000
- SIGN (22.0001), -- 1.0000
- SIGN (-22.01), -- -1.00
- SIGN (22.01) -- 1.00
On our wiki there is an article that shows you another 9 lesser know functions; these functions are
BINARY_CHECKSUM
COLUMNPROPERTY
DATALENGTH
ASCII, CHAR,UNICODE
NULLIF
PARSENAME
STUFF
REVERSE
GETUTCDATE
You can find that article here Ten SQL Server Functions That You Have Ignored Until Now
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.