question

jay3917 avatar image
jay3917 asked

Group and Concatenation aggregate to produce binary string

Scenario: I have a table that is used for storing a user's security on different PO series. And currently selecting a user's security produces the following record set.

alt text

each permission column has a boolean value and i would like to group each set on Job, SubJobNum and get a binary result (string if need be) that would result in the following format.

alt text

concatenation
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Matt Whitfield avatar image
Matt Whitfield answered

Assuming that the bit position of each bit is determined by use_series / 1000, then you would want something like this:

SELECT JobKey, JobNumber, JobSubJobNum, 
       SUM(use_POView * POWER(2, (use_series / 1000))) AS POView,
       SUM(use_POEdit * POWER(2, (use_series / 1000))) AS POEdit,
       SUM(use_POVoid * POWER(2, (use_series / 1000))) AS POVoid,
       SUM(use_PORec * POWER(2, (use_series / 1000))) AS PORec,
       SUM(use_ReqView * POWER(2, (use_series / 1000))) AS ReqView,
       SUM(use_ReqEdit * POWER(2, (use_series / 1000))) AS ReqEdit,
       SUM(use_ReqVoid * POWER(2, (use_series / 1000))) AS ReqVoid
  FROM [table]
GROUP BY JobKey, JobNumber, JobSubJobNum

This would give you integers, but you could then just query those as bit fields (which would be the most efficient way anyway).

The limit of this would be that your maximum use_series value would be 31000.

If you really wanted them as binary strings, then you could use a function which returned the binary string for a given integer.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.