Tuesday, November 10, 2020

SQL - Alternate Value In the Column

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;




No comments:

Post a Comment