March 18, 2014 at 3:08 am
I want to create a custom bitwise OR aggregate function.
I want to use it like the built in aggregate functions (MIN, MAX etc.)
SELECT dbo.bitwise_or(bit_string) FROM table
where bit_string is a nvarchar(3)
so for example if the table contains two rows ('100', '001') the above query should return '101'
I would like to implement this as a CLR function/assembly with the aggregate below:
CREATE AGGREGATE dbo.bitwise_or (bit_string nvarchar(3))
RETURNS [nvarchar(3)]
EXTERNAL NAME [Aggregate].[bitwise_or]
Can someone give me a jump start with the c# code?
I have followed this post to implement amedian aggregate function: http://www.sqlservercentral.com/articles/SQL+CLR/3208/
but there is a lot of code (not sure what is really needed in my case)
March 18, 2014 at 7:39 am
joakim.fenno (3/18/2014)
I want to create a custom bitwise OR aggregate function.I want to use it like the built in aggregate functions (MIN, MAX etc.)
SELECT dbo.bitwise_or(bit_string) FROM table
where bit_string is a nvarchar(3)
so for example if the table contains two rows ('100', '001') the above query should return '101'
I would like to implement this as a CLR function/assembly with the aggregate below:
CREATE AGGREGATE dbo.bitwise_or (bit_string nvarchar(3))
RETURNS [nvarchar(3)]
EXTERNAL NAME [Aggregate].[bitwise_or]
Can someone give me a jump start with the c# code?
I have followed this post to implement amedian aggregate function: http://www.sqlservercentral.com/articles/SQL+CLR/3208/
but there is a lot of code (not sure what is really needed in my case)
Why do you want to roll your own here? SQL has a bitwise OR already. Also, what you are describing is a scalar function which are notoriously bad for performance in sql server.
Here is the native sql code for what you want.
select 100|001
http://technet.microsoft.com/en-us/library/ms186714.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 18, 2014 at 8:38 am
I be happy to use the built in T-SQL functions if that is possible.
But the function needs to work as an aggregate function.
I have to be able to execute queries like the one below:
SELECT col1, col2, dbo.bitwise_or(bit_string)
FROM table
GROUP BY col1, col2
March 18, 2014 at 8:48 am
joakim.fenno (3/18/2014)
I be happy to use the built in T-SQL functions if that is possible.But the function needs to work as an aggregate function.
I have to be able to execute queries like the one below:
SELECT col1, col2, dbo.bitwise_or(bit_string)
FROM table
GROUP BY col1, col2
What you are describing is a scalar function (which the aggregates are too). Why do you think you have to use it like this?
Can you post some ddl and sample data along with the desired output. I am pretty sure you don't have to write this, you just need to figure out how to use it with your data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 18, 2014 at 8:53 am
joakim.fenno (3/18/2014)
I be happy to use the built in T-SQL functions if that is possible.But the function needs to work as an aggregate function.
I have to be able to execute queries like the one below:
SELECT col1, col2, dbo.bitwise_or(bit_string)
FROM table
GROUP BY col1, col2
If you can guarantee that your aggregate function would always and only ever generate the two values required by BITWISE OR, then you will find it far far easier to construct a query which puts the two values on the same row so that you can use the simple operator which Sean suggests. Don't turn something trivial into a spaghetti-gathering contest.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 18, 2014 at 9:04 am
OK
CREATE TABLE my_table (col1 nvarchar(25) NULL, bit_flag nvarchar(3) NULL)
INSERT INTO my_table VALUES('A', '100'), ('A', '001'),('B', '010'),('A', '001');
expected output from query :
A 101
B 011
The bit_flag column contains 12 flag (and is therefore a nvarchar(12)) in the real example
March 18, 2014 at 9:10 am
ChrisM@Work (3/18/2014)
If you can guarantee that your aggregate function would always and only ever generate the two values required by BITWISE OR, then you will find it far far easier to construct a query which puts the two values on the same row so that you can use the simple operator which Sean suggests. Don't turn something trivial into a spaghetti-gathering contest.
Thats not the case. Please have a look at the code I posted above.
March 18, 2014 at 9:12 am
joakim.fenno (3/18/2014)
ChrisM@Work (3/18/2014)
If you can guarantee that your aggregate function would always and only ever generate the two values required by BITWISE OR, then you will find it far far easier to construct a query which puts the two values on the same row so that you can use the simple operator which Sean suggests. Don't turn something trivial into a spaghetti-gathering contest.
Thats not the case. Please have a look at the code I posted above.
Here's a Technet entry for BITWISE operations.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 18, 2014 at 9:20 am
ChrisM@Work (3/18/2014)
joakim.fenno (3/18/2014)
ChrisM@Work (3/18/2014)
If you can guarantee that your aggregate function would always and only ever generate the two values required by BITWISE OR, then you will find it far far easier to construct a query which puts the two values on the same row so that you can use the simple operator which Sean suggests. Don't turn something trivial into a spaghetti-gathering contest.
Thats not the case. Please have a look at the code I posted above.
Here's a Technet entry for BITWISE operations.
Yes, '|' is the operator I will use, whether I use T-SQL or .NET.
March 18, 2014 at 9:25 am
joakim.fenno (3/18/2014)
ChrisM@Work (3/18/2014)
joakim.fenno (3/18/2014)
ChrisM@Work (3/18/2014)
If you can guarantee that your aggregate function would always and only ever generate the two values required by BITWISE OR, then you will find it far far easier to construct a query which puts the two values on the same row so that you can use the simple operator which Sean suggests. Don't turn something trivial into a spaghetti-gathering contest.
Thats not the case. Please have a look at the code I posted above.
Here's a Technet entry for BITWISE operations.
Yes, '|' is the operator I will use, whether I use T-SQL or .NET.
Between two operands. The code you've posted shows four.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 18, 2014 at 9:27 am
joakim.fenno (3/18/2014)
OK
CREATE TABLE my_table (col1 nvarchar(25) NULL, bit_flag nvarchar(3) NULL)
INSERT INTO my_table VALUES('A', '100'), ('A', '001'),('B', '010'),('A', '001');
expected output from query :
A 101
B 011
The bit_flag column contains 12 flag (and is therefore a nvarchar(12)) in the real example
This would be trivial from this (assuming that the last value should have 'B' for col1 instead of 'A'. However, you said your real table the data is different.
Here is how you could do this with the data you provided.
CREATE TABLE my_table (col1 nvarchar(25) NULL, bit_flag nvarchar(3) NULL)
INSERT INTO my_table VALUES('A', '100'), ('A', '001'),('B', '010'),('B', '001');
with SortedVals as
(
select col1, CAST(bit_flag as int) as bit_flag, ROW_NUMBER() over (partition by col1 order by bit_flag) as RowNum
from my_table
)
, CrossTabbedOutput as
(
select col1,
MAX(case when RowNum = 1 then bit_flag end) as b1,
MAX(case when RowNum = 2 then bit_flag end) as b2
from SortedVals s1
group by col1
)
select col1, b1|b2
from CrossTabbedOutput
drop table my_table
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 18, 2014 at 9:36 am
Although just about every reference on the first page returned by Google specifies two operands, the TSQL BITWISE OR actually works with a chain like this:
-- on a single row
SELECT 001 | 000 | 010
Result
11
Which I think can be emulated like this:
-- try row-wise
DECLARE @LastValue INT
SELECT @LastValue = 001
SELECT @LastValue = @LastValue | d.MyValue
FROM (SELECT MyValue = 000 UNION ALL SELECT 010) d
-- eyeball check
SELECT @LastValue
Result
11
- which can be converted into a msTVF.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 18, 2014 at 9:41 am
Sean Lange (3/18/2014)
joakim.fenno (3/18/2014)
OK
CREATE TABLE my_table (col1 nvarchar(25) NULL, bit_flag nvarchar(3) NULL)
INSERT INTO my_table VALUES('A', '100'), ('A', '001'),('B', '010'),('A', '001');
expected output from query :
A 101
B 011
The bit_flag column contains 12 flag (and is therefore a nvarchar(12)) in the real example
This would be trivial from this (assuming that the last value should have 'B' for col1 instead of 'A'. However, you said your real table the data is different.
Here is how you could do this with the data you provided.
CREATE TABLE my_table (col1 nvarchar(25) NULL, bit_flag nvarchar(3) NULL)
INSERT INTO my_table VALUES('A', '100'), ('A', '001'),('B', '010'),('B', '001');
with SortedVals as
(
select col1, CAST(bit_flag as int) as bit_flag, ROW_NUMBER() over (partition by col1 order by bit_flag) as RowNum
from my_table
)
, CrossTabbedOutput as
(
select col1,
MAX(case when RowNum = 1 then bit_flag end) as b1,
MAX(case when RowNum = 2 then bit_flag end) as b2
from SortedVals s1
group by col1
)
select col1, b1|b2
from CrossTabbedOutput
drop table my_table
Does not produce the correct output
A 101
B 11
instead of
A 101
B 011
and if I add 2 more rows to the table
INSERT INTO my_table VALUES('A', '010'),('B', '001');
I got
A11
B1
should be:
A111
B011
the solution has to work for an unknown number of rows
March 18, 2014 at 9:46 am
joakim.fenno (3/18/2014)
OK
CREATE TABLE my_table (col1 nvarchar(25) NULL, bit_flag nvarchar(3) NULL)
INSERT INTO my_table VALUES('A', '100'), ('A', '001'),('B', '010'),('A', '001');
expected output from query :
A 101
B 011
The bit_flag column contains 12 flag (and is therefore a nvarchar(12)) in the real example
You have three rows where col1 = 'A' and one row where col1 = 'B'. You have two rows in your result set, one for 'A' and one for 'B', and yet the result for 'B' is different to the value in the one row in the sample data. What is the third row in the sample data set, the row where col1 = 'B', OR'd with?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 18, 2014 at 9:50 am
ChrisM@Work (3/18/2014)
joakim.fenno (3/18/2014)
OK
CREATE TABLE my_table (col1 nvarchar(25) NULL, bit_flag nvarchar(3) NULL)
INSERT INTO my_table VALUES('A', '100'), ('A', '001'),('B', '010'),('A', '001');
expected output from query :
A 101
B 011
The bit_flag column contains 12 flag (and is therefore a nvarchar(12)) in the real example
You have three rows where col1 = 'A' and one row where col1 = 'B'. You have two rows in your result set, one for 'A' and one for 'B', and yet the result for 'B' is different to the value in the one row in the sample data. What is the third row in the sample data set, the row where col1 = 'B', OR'd with?
sorry, should be:
CREATE TABLE my_table (col1 nvarchar(25) NULL, bit_flag nvarchar(3) NULL)
INSERT INTO my_table VALUES('A', '100'), ('A', '001'),('B', '010'),('B', '001');
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply