Grouping Consecutive Data in rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Grouping Consecutive Data in rows

Author

Topic

x12179x
Starting Member

4 Posts

Posted - 2011-10-13 : 21:18:27

I have this table below:


CREATE TABLE dbo.Test
(
  ID INT IDENTITY(1,1) NOT NULL,
  Type    VARCHAR(50) NULL,
);

INSERT INTO  dbo.Test(Type) VALUES('A');
INSERT INTO  dbo.Test(Type)  VALUES('A');
INSERT INTO  dbo.Test(Type)  VALUES('A');
INSERT INTO  dbo.Test(Type)  VALUES('B');
INSERT INTO  dbo.Test(Type)  VALUES('B');
INSERT INTO  dbo.Test(Type)  VALUES('C');
INSERT INTO  dbo.Test(Type)  VALUES('A');
INSERT INTO  dbo.Test(Type)  VALUES('A');

ID | Type
1 | A
2 | A
3 | A
4 | B
5 | B
6 | C
7 | A
8 | A

and I want a result like this:

ID | Type | Grp
1 | A | 1
2 | A | 1
3 | A | 1
4 | B | 2
5 | B | 2
6 | C | 3
7 | A | 4
8 | A |4

Basically, they are grouped by the 'Type', but the problem is, the same 'Type' can occur downstream, but would be in a different group('Grp') because it is not part of the consecutive grouping.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 01:07:35

[code]
SELECT t.ID,t.Type,DENSE_RANK() OVER(ORDER BY COALESCE(t1.NextID,99999)) AS Grp
FROM Test t
OUTER APPLY (SELECT TOP 1 ID AS NextID
FROM Test
WHERE ID> t.ID
AND Type <> t.Type
ORDER BY ID ASC)t1

output
---------------------------------------------
1 A 1
2 A 1
3 A 1
4 B 2
5 B 2
6 C 3
7 A 4
8 A 4

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

x12179x
Starting Member

4 Posts

Posted - 2011-10-14 : 09:43:19

Thanks! It worked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 10:36:03

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources