Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have records with a bit field that are in an order how do I group them in sets of 3. For example if my first three rows have bit fields 0,1,0 I want them in set 0, the next 3 are 1,0,0 I want them in set 1. Next three are 0,0,1 and I want them in set 2.

Order is important as I'm matching against a pattern table to get an overall count of the binary patterns.

I can see that Row would help but I don't know how to make row count by 3'ss.

Thank you.

share|improve this question
    
look at this question on StackOverflow: stackoverflow.com/q/7144100/136639, I hope it helps –  Alex_L Aug 23 '11 at 1:45
2  
How are you identifying "first three rows"? what version of SQL Server? –  Aaron Bertrand Aug 23 '11 at 2:32
    
If you have 3 bit columns, there are 8 permutations unless you are saying that only one column or the 3 can be set. Or do you have one bit column where you want to group adjacent rows: we need to know what makes "adjacent". And are there any other bit patterns eg 110? –  gbn Aug 23 '11 at 4:38
add comment

3 Answers

Rows in a table don't have order. You will need an additional column to enforce the order, if order is important for you.

share|improve this answer
add comment

If you have a column that preserves order, here is how you could do it in Oracle. You will have to translate to SQL Server.

SELECT f1a, f1b, f1c, count(*) FROM (
   SELECT LAG(f1,2) OVER (ORDER BY f2) f1a, LAG(f1, 1) OVER (ORDER BY f2) f1b, f1 f1c, f2 
   from t1
   )
WHERE MOD(f2,3) = 0
GROUP BY f1a, f1b, f1c;

Test Data:

--DROP TABLE t1;
CREATE TABLE t1 AS 
   SELECT 0 f1, 1 f2 from dual
   UNION ALL
   SELECT 1, 2 from dual
   UNION ALL
   SELECT 0, 3 from dual
   UNION ALL
   SELECT 1, 4 from dual
   UNION ALL
   SELECT 0, 5 from dual
   UNION ALL
   SELECT 0, 6 from dual
   UNION ALL
   SELECT 0, 7 from dual
   UNION ALL
   SELECT 0, 8 from dual
   UNION ALL
   SELECT 1, 9 from dual
   UNION ALL
   SELECT 1, 10 from dual
   UNION ALL
   SELECT 0, 11 from dual
   UNION ALL
   SELECT 0, 12 from dual
;
share|improve this answer
    
+1 I should have read this before answering :) –  Jack Douglas Aug 23 '11 at 18:34
add comment

Based on the following assumptions:

  1. You already know what order you want your data in and how to do it - in particular, your ordering does not allow ties
  2. You have 1 bit field in each row
  3. You want to concatenate the bit fields in each 'set' of 3 'adjacent' rows
  4. Your version of SQL Server supports windowing functions

You can use the windowing function row_number() and divide by three to group the rows into 3s, perhaps like this:

testbed:

create table t(id int identity, flag bit default round(rand(),0))
while (select count(*) from t)<21
begin
  insert into t default values
end

query:

select grp, str(sum(case rnk%3 when 0 then cast(flag as integer) end))+
            str(sum(case rnk%3 when 1 then cast(flag as integer) end))+
            str(sum(case rnk%3 when 2 then cast(flag as integer) end)) pattern
from( select id, flag, row_number() over (order by id) - 1 as rnk, 
             floor((row_number() over (order by id) - 1)/3) as grp
      from t ) as z
group by grp

result:

grp          pattern
0            0         1         0
1            0         0         0
2            1         1         1
3            0         1         0
4            0         0         0
5            1         0         1
6            1         0         0

You'll need to substitute your ordering for my order by ids - and probably do something with the pattern to make it match with your pattern table :)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.