Post reply

Aggregate bit operator in TSQL

  • December 21, 2008 at 10:38 pm

    #197489

    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

  • December 21, 2008 at 10:58 pm

    #915961

    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

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 22, 2008 at 4:08 pm

    #916460

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 22, 2008 at 4:29 pm

    #916467

    Yeah, I always forget that T-SQL has the SIGN() function.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 22, 2008 at 5:18 pm

    #916486

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 22, 2008 at 7:04 pm

    #916516

    Well it's shorter than CASE, but not much beyond that.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 23, 2008 at 5:11 am

    #916691

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 23, 2008 at 7:41 am

    #916754

    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".

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 23, 2008 at 8:13 am

    #916777

    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.

    [font="Courier New"]Bit Mask Decimal Value

    0000000001 1

    0000000101 5

    0000000111 7

    0000010001 17

    0000010011 19

    0000011101 29[/font]

    [font="Arial Narrow"](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.[/font]

  • December 23, 2008 at 8:25 am

    #916792

    JohnG; Actually, we've been talking about the BIT datatype. I think that you are talking about bit-masks encoded into INT's.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 23, 2008 at 8:31 am

    #916797

    RBarryYoung (12/23/2008)

    JohnG; Actually, we've been talking about the BIT datatype. I think that you are talking about bit-masks encoded into INT's.

    Exactly.

    I will add that the use of SIGN needs to be in one's arsenal. I've used it quite often.

    P.S. We're both dating ourselves. I started with FORTRAN IV, then COBOL, among others and have been "flipping bits" for quite some time. Note my avatar.

    [font="Arial Narrow"](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.[/font]

  • December 23, 2008 at 8:55 am

    #916828

    Heh, I started with half-adder circuit diagrams when I was 13. Then FORTRAN II, then original BASIC, then COBOL, etc...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 23, 2008 at 5:33 pm

    #917166

    JohnG (12/23/2008)

    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.

    [font="Courier New"]Bit Mask Decimal Value

    0000000001 1

    0000000101 5

    0000000111 7

    0000010001 17

    0000010011 19

    0000011101 29[/font]

    Yep... "pseudo-cursor"... just like you had...

    --===== Create the test data

    -- (NOT PART OF THE SOLUTION)

    DECLARE @SomeTable TABLE

    (BitMask INT)

    INSERT INTO @SomeTable

    (BitMask)

    SELECT 1 UNION ALL

    SELECT 5 UNION ALL

    SELECT 7 UNION ALL

    SELECT 17 UNION ALL

    SELECT 19 UNION ALL

    SELECT 29

    --===== OR it all together

    DECLARE @OredData INT

    SELECT @OredData = ISNULL(@OredData,0)|BitMask

    FROM @SomeTable

    --===== Display the result

    -- (NOT PART OF THE SOLUTION)

    SELECT @OredData

    --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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 24, 2008 at 7:23 am

    #917378

    Jeff Moden (12/23/2008)

    Yep... "pseudo-cursor"... just like you had...

    Thanks, Jeff for taking the time to look at the problem.

    The solution implemented (pseudo-cursor) is working quite well in our application. But I always had this thought in the back of my mind that there was some algorithm or technique out there that would solve the problem using set-based logic. Glad to know that I can sleep better.

    Side note: Oracle provides the capability to write your own Aggregate Functions, using the ODCIAggregate interface routines, which could be used here to solve the problem. However, I didn't pursue it as it didn't have the ROI for the portable (SQL Server and Oracle) implementation and the cursor solution was performing well.

    [font="Tahoma"]User-defined aggregate functions are used in SQL DML statements just like Oracle’s own built-in aggregates. Once such functions are registered with the server, Oracle simply invokes the aggregation routines that you supplied instead of the native ones.[/font]

    [font="Arial Narrow"](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.[/font]

  • December 24, 2008 at 8:51 am

    #917439

    JohnG (12/24/2008)

    The solution implemented (pseudo-cursor) is working quite well in our application. But I always had this thought in the back of my mind that there was some algorithm or technique out there that would solve the problem using set-based logic.

    Oh there is, but it is way ugly and not necessarily any faster (or even as fast).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy