Group of neighbour entries

Question

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

This should help:

SELECT * into #tmp_table from

(

SELECT 1 AS ID, 'ProductA' AS Code, 5 AS Quantity

UNION ALL

SELECT 2 AS ID, 'ProductA' AS Code, 5 AS Quantity

UNION ALL

SELECT 3 AS ID, 'ProductA' AS Code, 5 AS Quantity

UNION ALL

SELECT 4 AS ID, 'ProductB' AS Code, 5 AS Quantity

UNION ALL

SELECT 5 AS ID, 'ProductB' AS Code, 5 AS Quantity

UNION ALL

SELECT 6 AS ID, 'ProductA' AS Code, 5 AS Quantity

UNION ALL

SELECT 7 AS ID, 'ProductA' AS Code, 5 AS Quantity

) as tmp

;WITH TmpCTE AS (

SELECT Id, Code, Quantity,

ROW_ID = ROW_NUMBER() OVER (ORDER BY ID),

ROW_ID_GRP = ROW_NUMBER() OVER(PARTITION BY Code ORDER BY ID)

FROM #tmp_table

),

RankedCTE AS (

SELECT *, GROUP_ID = ROW_ID - ROW_ID_GRP

FROM TmpCTE

)

SELECT Code, SUM(Quantity)

FROM RankedCTE

GROUP BY GROUP_ID, Code

ORDER BY MIN(ID)

drop table #tmp_table

-- Gianluca Sartori

brnj SSC Rookie Points: 44 More actions · Answer 2

Hi Gianluca,

this seems to be the solution.

Thanks a lot. You are great!

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

You're welcome.

Glad I could help.

-- Gianluca Sartori