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:
- Create a table structure
- Insert the data into the table
- Apply the Row_Number function with the partition
- 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
No comments :
Post a Comment