Find same value set in adjacent rows in pair with TSQL



 
 CREATE TABLE [dbo].[DATA1](
[Identity] [int] not null,
[C1] [float] NULL,
[C2] [float] NULL,
[C3] [float] NULL,
[C4] [float] NULL,
[C5] [float] NULL,
[C6] [float] NULL,
[C7] [float] NULL,
[C8] [float] NULL
) ON [PRIMARY]

GO

INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (15, 2, 6, 7, 9, 11, 12, 13, 18)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (14, 2, 4, 7, 8, 10, 11, 14, 15)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (13, 2, 3, 4, 7, 8, 11, 12, 16)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (12, 4, 5, 9, 10, 11, 14, 16, 19)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (11, 1, 3, 5, 7, 8, 11, 12, 15)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (10, 1, 3, 4, 7, 13, 14, 17, 19)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (9, 1, 2, 5, 6, 8, 10, 12, 15)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (8, 1, 4, 6, 12, 14, 15, 16, 17)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (7, 2, 3, 8, 9, 10, 12, 15, 17)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (6, 2, 4, 5, 6, 7, 11, 12, 13)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (5, 1, 2, 9, 10, 11, 13, 14, 15)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (4, 1, 3, 6, 8, 9, 10, 11, 12)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (3, 1, 5, 8, 10, 12, 13, 14, 15)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (2, 5, 6, 7, 11, 12, 13, 15, 18)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (1, 5, 7, 10, 12, 14, 15, 16, 17)
GO

;with mycte as (
select [identity], c1,c2,c3,c4,c5,c6,c7,c8
,SUM([identity]%2) Over(Order by  [identity]) grp 

from data1)
,mycte1 as
(
Select [identity] 
,Case 
when c1=Lead(c1,1)OVER (Partition by grp  ORDER BY  [identity]) Then c1 
when c1=Lag(c1,1)OVER (Partition by grp  ORDER BY  [identity]) Then c1 else null End c1
,Case 
when c2=Lead(c2,1)OVER (Partition by grp  ORDER BY  [identity]) Then c2 
when c2=Lag(c2,1)OVER (Partition by grp  ORDER BY  [identity]) Then c2 else null End c2
,Case 
when c3=Lead(c3,1)OVER (Partition by grp  ORDER BY  [identity]) Then c3 
when c3=Lag(c3,1)OVER (Partition by grp  ORDER BY  [identity]) Then c3 else null End c3
,Case 
when c4=Lead(c4,1)OVER (Partition by grp  ORDER BY  [identity]) Then c4 
when c4=Lag(c4,1)OVER (Partition by grp  ORDER BY  [identity]) Then c4 else null End c4
,Case 
when c5=Lead(c5,1)OVER (Partition by grp  ORDER BY  [identity]) Then c5 
when c5=Lag(c5,1)OVER (Partition by grp  ORDER BY  [identity]) Then c5 else null End c5
,Case 
when c6=Lead(c6,1)OVER (Partition by grp  ORDER BY  [identity]) Then c6 
when c6=Lag(c6,1)OVER (Partition by grp  ORDER BY  [identity]) Then c6 else null End c6
,Case 
when c7=Lead(c7,1)OVER (Partition by grp  ORDER BY  [identity]) Then c7 
when c7=Lag(c7,1)OVER (Partition by grp  ORDER BY  [identity]) Then c7 else null End c7
,Case 
when c8=Lead(c8,1)OVER (Partition by grp  ORDER BY  [identity]) Then c8 
when c8=Lag(c8,1)OVER (Partition by grp  ORDER BY  [identity]) Then c8 else null End c8
 
from mycte)

SELECT [identity],
       c1,
       c2,
       c3,
       c4,
       c5,
       c6,
       c7,
       c8
FROM   mycte1 



drop table data1



 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f78fcd05-874d-43e2-b780-42e6cc71cba6/need-help-with-query?forum=transactsql#0c7e88ad-72ef-47ee-a167-0e45ecefd56c

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