July 31, 2014 at 2:40 pm
Hi Guys,
I have an interesting problem I am trying to solve without using cursors...
I have a data set that looks something like like this:
Row# Data
1 A
2 B
3 B
4 A
5 B
6 B
7 A
8 A
9 A
I need wanting to assign a group ID to the data based on consecutive values. Here's what I need my data to look like:
Row# Data GroupID
1 A 1
2 B 2
3 B 2
4 A 3
5 B 4
6 B 4
7 A 5
8 A 5
9 A 5
You'll notice that there are only two values in DATA but whenever there is a flip between them, the GroupID increments.
Any suggestions?
July 31, 2014 at 2:59 pm
Straight forward running total and lag
😎
;WITH BASE AS
(
SELECT [R], [D]
FROM (
VALUES
(1,'A')
,(2,'B')
,(3,'B')
,(4,'A')
,(5,'B')
,(6,'B')
,(7,'A')
,(8,'A')
,(9,'A')) AS X([R],[D]))
,PRIME_GROUP AS
(
SELECT
B.R
,B.D
,CASE WHEN LAG(B.D,1,'') OVER (ORDER BY B.R) = B.D THEN 0 ELSE 1 END AS GR_ID
FROM BASE B
)
SELECT
PG.R
,PG.D
,SUM(PG.GR_ID) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY PG.R
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS GroupID
FROM PRIME_GROUP PG;
Results
R D GroupID
-- -- -------
1 A 1
2 B 2
3 B 2
4 A 3
5 B 4
6 B 4
7 A 5
8 A 5
9 A 5
July 31, 2014 at 3:23 pm
Thanks! Now I have to go learn how you did some of this. Thanks a ton!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply