Total Page Preview:   000000002117

How to get the alternate rows or records from table in sql server

In this article I am going to explain How to get alternate row from table in sql server with expample using sample and CTE query.

Get the alternate row or record to Gender column:

---------------------Get the alternate row by the Gender--------------------
 
SELECT NAME, GENDER, ROW_NUMBER() OVER(PARTITION BY GENDER ORDER BY GENDER) AS R
FROM #PERSON ORDER BY R, GENDER DESC
 

Get the alternate row or record to Gender column Using CTE:

---------------------Get the alternate row by the Gender using CTE--------------------
 
;WITH PRS (Name, Gender, R)
AS
(
SELECT NAME, GENDER, ROW_NUMBER() OVER(PARTITION BY GENDER ORDER BY GENDER) AS R
FROM #PERSON
)
SELECT NAME, GENDER FROM PRS ORDER BY R, GENDER DESC
 
 
 
 
 
 
 
Get SQL Scripts for Create Tables and Insert Data for demo:
 
---------------Create table ------------
CREATE TABLE #PERSON
(
     NAME        VARCHAR(60),
    GENDER    VARCHAR(1)
)  
 
--------------------------------- Insert record into table------------------------
INSERT INTO #PERSON (NAME, GENDER) VALUES ('Brijesh', 'M')
INSERT INTO #PERSON (NAME, GENDER) VALUES ('Rahul', 'M')
INSERT INTO #PERSON (NAME, GENDER) VALUES ('Saurabh', 'M')
INSERT INTO #PERSON (NAME, GENDER) VALUES ('Arti', 'F')
INSERT INTO #PERSON (NAME, GENDER) VALUES ('Jyoti', 'F')
INSERT INTO #PERSON (NAME, GENDER) VALUES ('Preeti', 'F')
 
 
 
Thanks !!!
Brijesh Kumar
 

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments


                           
                           

                           

                           

Facebook User: