Query The Table To Find Out the Column Values in the pattern alternately, Boy and Girl.
CREATE Table [Data]
(
ID INT,
[Col A] NVARCHAR(10),
[Col B] NVARCHAR(10),
[Col C] NVARCHAR(10)
);
--drop table [data]
INSERT INTO [data] (ID, [Col C]) VALUES
(1, 'Girl'),
(2, 'Boy'),
(3, 'Girl'),
(4, 'Boy'),
(5, 'Girl'),
(6, 'Boy'),
(7, 'Girl'),
(8, 'Boy'),
(9, 'Girl'),
(10,'Boy');
---OR
SELECT d.ID,
d.[Col C],
CASE WHEN ( d.[Col C] = coalesce(lag(d.[Col C]) OVER (ORDER BY d.ID), '')
and d.[Col C] = coalesce(lead(d.[Col C]) OVER (ORDER BY d.ID), '') ) THEN 'FAIL'
WHEN d.[Col C] <> coalesce(lag(d.[Col C]) OVER (ORDER BY d.ID), '') THEN 'Ok' -- First Line
WHEN d.[Col C] <> coalesce(lead(d.[Col C]) OVER (ORDER BY d.ID), '') THEN 'Ok' -- Last Line
END AS B
FROM [DATA] d
ORDER BY d.ID;
ID INT,
[Col A] NVARCHAR(10),
[Col B] NVARCHAR(10),
[Col C] NVARCHAR(10)
);
--drop table [data]
INSERT INTO [data] (ID, [Col C]) VALUES
(1, 'Girl'),
(2, 'Boy'),
(3, 'Girl'),
(4, 'Boy'),
(5, 'Girl'),
(6, 'Boy'),
(7, 'Girl'),
(8, 'Boy'),
(9, 'Girl'),
(10,'Boy');
d.[Col C],
CASE WHEN ( d.[Col C] = coalesce(lag(d.[Col C]) OVER (ORDER BY d.ID), '')
and d.[Col C] = coalesce(lead(d.[Col C]) OVER (ORDER BY d.ID), '') ) THEN 'FAIL'
WHEN d.[Col C] <> coalesce(lag(d.[Col C]) OVER (ORDER BY d.ID), '') THEN 'Ok' -- First Line
WHEN d.[Col C] <> coalesce(lead(d.[Col C]) OVER (ORDER BY d.ID), '') THEN 'Ok' -- Last Line
END AS B

No comments:
Post a Comment