Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Aggregate bit operator in TSQL Expand / Collapse
Author
Message
Posted Sunday, December 21, 2008 10:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 27, 2009 10:29 PM
Points: 2, Visits: 39
Hello All:

Do we have any aggregate bit operator in TSql?

Consider the following scenario
I have a table as follows
Col1 Col2 Col3
1 1 0
0 0 1
1 0 0

Now I want to perform a select on the above table such that I get a single row where each column is a OR of that column for all rows

For e.g
SELECT func(Col1), func(Col2), func(Col3)

Output
1, 1, 1

Thanks,
Vishnu
Post #623717
Posted Sunday, December 21, 2008 10:58 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
There is no such function. However, you can emulate this with the following:
Select
Case When Sum(Cast(Col1 as int)) > 1 Then 1 Else 0 End



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #623726
Posted Monday, December 22, 2008 4:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 35,589, Visits: 32,179
Here's a real cheater method...

 SELECT SIGN(SUM(Col1+0)), SIGN(SUM(Col2+0)), SIGN(SUM(Col3+0))
FROM SomeTable



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #624324
Posted Monday, December 22, 2008 4:29 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
Yeah, I always forget that T-SQL has the SIGN() function.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #624332
Posted Monday, December 22, 2008 5:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 35,589, Visits: 32,179
Heh... and it works so well for these types of bitwise ops.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #624352
Posted Monday, December 22, 2008 7:04 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
Well it's shorter than CASE, but not much beyond that.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #624386
Posted Tuesday, December 23, 2008 5:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 35,589, Visits: 32,179
I agree... CASE is almost as fast and bit easier to read.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #624606
Posted Tuesday, December 23, 2008 7:41 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
And it's so much more general. The reason that I keep forgetting that SIGN() is in SQL is not because I am not familiar with it. Heck, I used it often as SGN() back in the 70's in both FORTRAN and BASIC. Rather, I am sure that it's the very generality of the CASE function that leads me to subliminally lump SIGN() inot the category of "things that I don't have to remember".

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #624679
Posted Tuesday, December 23, 2008 8:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:22 AM
Points: 1,187, Visits: 1,984
Jeff Moden (12/22/2008)
Here's a real cheater method...

 SELECT SIGN(SUM(Col1+0)), SIGN(SUM(Col2+0)), SIGN(SUM(Col3+0))
FROM SomeTable



That only works for the first bit. I have a similar problem where we use the first 5 bits as "permissions" on objects and I need to OR the values from multiple rows. Unfortunately, I'm performing RBAR, albeit without a cursor (using SQL Server's multiple row select into a scalar variable approach), in a UDF. E.g.,
SELECT @v_permissions = ISNULL(@v_permissions, 0) | x.permissions FROM ...

Any T-SQL tricks? E.g., the following values need to be OR'd together with the result being (decimal) 31.
Bit Mask Decimal Value
0000000001 1
0000000101 5
0000000111 7
0000010001 17
0000010011 19
0000011101 29






(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #624703
Posted Tuesday, December 23, 2008 8:25 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
JohnG; Actually, we've been talking about the BIT datatype. I think that you are talking about bit-masks encoded into INT's.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #624720
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse