An Aggregation Query with T-SQL (Two Solutions: SQL Server 2012 only and SQL Server 2008/05)



if object_id('dbo.t','u') is not null
DROP TABLE [dbo].[t]
GO

CREATE TABLE [dbo].[t](
	[TransactType] [varchar](20) NULL,
	[TansactionCnt] [int] NULL,
	[TansactionSequence] [int] NULL
) ON [PRIMARY]

GO




INSERT [dbo].[t]   VALUES (N'Payment', 1, 600008)
, (N'New_Order', 1, 600010)
,(N'Payment', 1, 600012)
,(N'Payment', 1, 600014)
,(N'OrderStatus', 1, 600016)
,(N'Delivery', 1, 600018)
,(N'New_Order', 1, 600020)
,(N'New_Order', 1, 600022)
,(N'New_Order', 1, 600024)
, (N'Payment', 1, 600026)

;WITH mycte AS (
	SELECT TransactType,	TansactionCnt,	TansactionSequence,
 ROW_NUMBER() OVER(ORDER BY TansactionSequence) AS rnAll,
 ROW_NUMBER() OVER(PARTITION BY TransactType ORDER BY TansactionSequence) AS rnTransactType
FROM t 

), mycte1 AS (
   SELECT   TransactType, count(TransactType) Over(partition by TransactType, rnAll - rnTransactType) as TansactionCnt, TansactionSequence
FROM  mycte )


SELECT   TransactType, TansactionCnt, 
(STUFF((SELECT ',' + CAST(TansactionSequence AS varchar(10)) FROM  mycte1 WHERE m.TransactType=TransactType and m.TansactionCnt=TansactionCnt
 FOR XML PATH('')), 1, 1, '')) AS TansactionSequences
FROM  mycte1 AS m
GROUP BY TransactType, TansactionCnt
Order by TansactionSequences
--PS:
--I have learned this technique from Erland's sample at:
--http://social.msdn.microsoft.com/Forums/en/transactsql/thread/339af246-12e9-4c82-8d26-c3f23f126017

–SQL Server 2012 Solution


;with mycte0 as
(
select TransactType, TansactionSequence,
ROW_NUMBER() OVER(ORDER BY TansactionSequence) AS rn,
Case  When TransactType= lag(TransactType,1) Over(Order by TansactionSequence) Then 0 else 1 END resetflag 
FROM t --yourtablename
)

,mycte
as
(select TransactType, TansactionSequence, sum(resetflag) Over(order by rn) grn from mycte0)

,mycte1 as
(
select TransactType,  count(TansactionSequence) Over(partition by grn) TansactionCnt, TansactionSequence from mycte)


SELECT TransactType, TansactionCnt, 
(STUFF((SELECT  CONCAT(', ', CAST(TansactionSequence AS varchar(10))) FROM  mycte1 WHERE m.TransactType=TransactType and m.TansactionCnt=TansactionCnt
 FOR XML PATH('')), 1, 1, '')) AS TansactionSequences
FROM  mycte1 AS m
GROUP BY TransactType, TansactionCnt
Order by TansactionSequences
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s