Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Assigning a "Group ID" based on consecutive Values Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, July 31, 2014 2:40 PM
 Forum Newbie Group: General Forum Members Last Login: Thursday, July 31, 2014 3:21 PM Points: 2, Visits: 1
 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# Data1 A2 B3 B4 A5 B6 B7 A8 A9 AI 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 GroupID1 A 12 B 23 B 24 A 35 B 46 B 47 A 58 A 59 A 5You'll notice that there are only two values in DATA but whenever there is a flip between them, the GroupID increments.Any suggestions?
Post #1598492
 Posted Thursday, July 31, 2014 2:59 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 3:59 AM Points: 6,545, Visits: 17,215
 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 GroupIDFROM PRIME_GROUP PG;`Results`R D GroupID-- -- -------1 A 12 B 23 B 24 A 35 B 46 B 47 A 58 A 59 A 5`
Post #1598498
 Posted Thursday, July 31, 2014 3:23 PM
 Forum Newbie Group: General Forum Members Last Login: Thursday, July 31, 2014 3:21 PM Points: 2, Visits: 1
 Thanks! Now I have to go learn how you did some of this. Thanks a ton!
Post #1598517

 Permissions