MS Sql Server Row_Number() Interview Question 2023

 Agenda :

            Dear all In this article, I would like to demonstrate the MS SQL Server Row_Number() Interview Question 2023

Description :
          In previous articles,I have explained How to work with File upload control using Asp.Net – PART - I, Working with File Upload control using Asp.Net – PART - II,How to resolve maximum requested length exceeded error with file upload control – PART - III different articles related to C#.Net,Asp.Net, SQL Server, XML, and DotNetInterviewQuestions Related articles. Now Let's work on MS SQL Server Row_Number() Interview Question 2023

Steps to work with this:

1. Create a table structure
2. Insert the data into the table
3. Apply the Row_Number function with the partition
4. Apply the case statement with Row_Number check and concatenate the GCCode with Row_Number

Video Explanation:

Create the table structure:

CREATE TABLE tbl_2023_IQ1(GiftCardId INT IDENTITY,GcCode VARCHAR(20),GCValue DECIMAL(18,3))

Insert the data into the table:

INSERT INTO tbl_2023_IQ1(GcCode,GCValue) VALUES ('GC001','200'),

('GC002','100'),('GC002','300'),('GC004','100'),('GC004','300'),('GC004','10'),('GC006','500'),

('GC007','1000')

Apply the Row_Number function with the partition:

SELECT ROW_NUMBER() OVER(PARTITION BY GCCode ORDER BY GiftCardID),* FROM tbl_2023_IQ1

Apply the case statement with Row_Number check and concatenate the GCCode with Row_Number:

SELECT CASE WHEN ROW_NUMBER() OVER(PARTITION BY GCCode ORDER BY GiftCardID) = 1 THEN GCCode 

ELSE GCCode+'_'+CAST(CAST(ROW_NUMBER() OVER(PARTITION BY GCCode ORDER BY GiftCardID) AS INT)-1 AS VARCHAR(100))

END AS GiftCardCode FROM tbl_2023_IQ1