CODEWITHSUNDEEP
sql-server
tr3v
tr3v

Reputation: 431

How do I group rows by a sequence in SQL

I have a table (Microsoft SQL Server 2012), that I need to group in a report by col1, but including breaks determined by the order of col2 (in this simplified example). 

col1 col2
A    1
A    2
A    3
B    4
A    5
C    6

Grouping by col1 gives me 3 groups (A,B,C), but I need A: 1,2,3 to be separate from A: 5, giving me 4 groups in the above example: 

A: 1,2,3
B: 4
A: 5
C: 6

I have tried 

SELECT row_number() OVER (PARTITION BY col1 order by col2) as GroupNumber, col1, col2
FROM MyTable

but that just gives me a sequence number within each group. What I really want is some way of numbering each group but am well and truly stuck!

Edit

For clarification, I would like to have a result looking similar to the following:-

grp col1 col2
1    A    1
1    A    2
1    A    3
2    B    4
3    A    5
4    C    6

Upvotes: 0

Views: 78

Answers (2)

Marco Bong
Marco Bong

Reputation: 690

declare @tb as table (col1 nvarchar(5), col2 int);
insert into @tb values ('A',1)
insert into @tb values ('A',2)
insert into @tb values ('A',3)
insert into @tb values ('B',4)
insert into @tb values ('A',5)
insert into @tb values ('C',6)

select grp.grpNo,org.col1,org.col2 from @tb org
inner join (select row_number() over (order by t1.col1) as grpNo, t1.col1 from
(select col1 from @tb group by col1) t1) grp on org.col1 = grp.col1 order by grp.grpNo

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

Using ROW_NUMBER:

;WITH Cte AS(
    SELECT *,
        grp = col2 - ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)
    FROM MyTable
)
SELECT * FROM Cte ORDER BY col1, col2

For your desired result:

;WITH Cte AS(
    SELECT *,
        grp = col2 - ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)
    FROM MyTable
)
SELECT
    c.col1,
    x.col2
FROM Cte c
CROSS APPLY (
    SELECT STUFF((
        SELECT ',' + CONVERT(VARCHAR(10), col2)
        FROM Cte 
        WHERE
            col1 = c.col1
            AND grp = c.grp
        ORDER BY col2
        FOR XML PATH('')
    ), 1, 1,'')
) x(col2)
GROUP BY c.col1, c.grp, x.col2

Upvotes: 3

Related Questions