Repeat Rows N Times According to Column Value in SQL Server : HCL Interview Question

Suppose you have a table #Temp as shown in the below image(left table) and you want to repeat rows based on "NTimes" column valu...

Suppose you have a table #Temp as shown in the below image(left table) and you want to repeat rows based on "NTimes" column value. Then what will you do to generate output as below(right hand side table), asked during HCL interview

Ans:


SELECT A.Name,[NTimes] FROM
(SELECT Name,[NTimes], CAST(('<val>'+ REPLICATE ( Name+'</val><val>' ,[NTimes]-1 ) +'</val>') AS XML) AS X  FROM #Temp) A
CROSS APPLY A.X.nodes('/val') y(z)


Related

SQL SERVER INTERVIEW QUESTIONS 8736101787620726907

Post a comment

  1. SELECT A.Name,[NTimes]
    --, z.value('.', 'varchar(100)') R -- Not required but you can use it to disply our new column value
    FROM (SELECT Name,[NTimes], CAST((''+ REPLICATE ( Name+'' ,[NTimes]-1 ) +'') AS XML) AS X FROM #Temp) A
    CROSS APPLY A.X.nodes('/val') y(z)

    ReplyDelete
  2. --SELECT * INTO #EMarks FROM (VALUES(1,'A',60),(2,'B',90),(3,'C',70),(4,'D',55)) V(ID,SName,EMarks)

    --SELECT * INTO #SMarks FROM (VALUES(1,'A',50),(2,'B',80),(3,'C',70),(4,'D',55)) V(ID,SName,SMarks)

    --SELECT * INTO #BMarks FROM (VALUES(1,'A',40),(2,'B',80),(3,'C',70),(4,'D',55)) V(ID,SName,BMarks)

    --SELECT E.ID,E.SName,E.EMarks,S.SMarks,B.BMarks,
    --IIF(E.EMarks BETWEEN 80 AND 100,'Marit',(IIF (E.EMarks BETWEEN 60 AND 79,'Pass','Fail'))) E,
    --IIF(S.SMarks BETWEEN 80 AND 100,'Marit',(IIF (S.SMarks BETWEEN 60 AND 79,'Pass','Fail'))) S,
    --IIF(B.BMarks BETWEEN 80 AND 100,'Marit',(IIF (B.BMarks BETWEEN 60 AND 79,'Pass','Fail'))) B
    --FROM #EMarks E INNER JOIN
    --#SMarks S ON E.ID = S.ID INNER JOIN #BMarks B ON B.ID = E.ID

    SELECT *, IIF((E = S AND (E='Pass' OR E='Marit')), E , IIF((E = 'Fail' OR S = 'Fail'),'Fail',(IIF(B='Marit' OR B='Pass',B,'Fail'))))
    FROM (
    SELECT E.ID,E.SName,E.EMarks,S.SMarks,B.BMarks,
    IIF(E.EMarks BETWEEN 80 AND 100,'Marit',(IIF (E.EMarks BETWEEN 60 AND 79,'Pass','Fail'))) E,
    IIF(S.SMarks BETWEEN 80 AND 100,'Marit',(IIF (S.SMarks BETWEEN 60 AND 79,'Pass','Fail'))) S,
    IIF(B.BMarks BETWEEN 80 AND 100,'Marit',(IIF (B.BMarks BETWEEN 60 AND 79,'Pass','Fail'))) B
    FROM #EMarks E INNER JOIN
    #SMarks S ON E.ID = S.ID INNER JOIN #BMarks B ON B.ID = E.ID
    ) M

    --UPDATE #eMarks SET eMarks = 90 WHERE sname ='c'

    --SELECT * FROM #EMarks

    ReplyDelete
    Replies
    1. Sorry for this comment... by mistake its not related to this post

      Delete
    2. IMPORTENT INDEX QUERY
      SELECT * FROM Profile1
      SELECT Page_Count, Index_depth, Page_level = index_level, Page_count,Record_count, * FROM sys.dm_db_index_physical_stats(db_id(),object_id('Profile2'),null,null,'DETAILED')

      select allocated_page_page_id, next_page_page_id, previous_page_page_id from sys.dm_db_database_page_allocations(db_id(),object_id('Profile2'),null,null,'DETAILED')
      where page_type_desc is not null and page_type_desc = 'DATA_PAGE'

      select allocated_page_page_id, next_page_page_id, previous_page_page_id from sys.dm_db_database_page_allocations(db_id(),object_id('Profile2'),null,null,'DETAILED')
      where page_type_desc is not null and page_type_desc = 'DATA_PAGE'


      select allocated_page_page_id, next_page_page_id, previous_page_page_id, page_level, page_type_desc
      from sys.dm_db_database_page_allocations(db_id(),object_id('Profile2'),null,null,'DETAILED')
      where page_type_desc is not null and page_type_desc IN( 'DATA_PAGE', 'INDEX_PAGE')
      ORDER BY CASE WHEN page_type_desc = 'INDEX_PAGE' THEN 0 ELSE allocated_page_page_id END ASC

      SELECT OBJECT_NAME(261575970)
      179470

      179470 179758
      358938

      358939
      3761
      DBCC PAGE('WorkDB',1,3762,3) WITH TABLERESULTS
      DBCC PAGE('WorkDB',1,5001,3) WITH TABLERESULTS

      Delete
  3. CREATE TABLE Table1
    (
    Name VARCHAR(50),
    Value INT
    )
    GO

    INSERT INTO Table1 VALUES('Aryan',4),('Amit',3),('Neeraj',2),('Kapil',5)

    SELECT * FROM Table1

    DECLARE @LoopCounter INT = 1, @MaxEmployeeId INT = 0 ,
    @Name NVARCHAR(100),@NameValue INT=0,@Value INT,
    @InnerLoopCount INT=1;

    SELECT @MaxEmployeeId=COUNT(1) FROM Table1
    WHILE(@LoopCounter <= @MaxEmployeeId)
    BEGIN
    --print @LoopCounter;
    SELECT TOP(@LoopCounter) @NameValue=Value FROM Table1
    --print @NameValue
    WHILE(@InnerLoopCount<=@NameValue)
    BEGIN
    SELECT @Name = Name,@Value=Value
    FROM Table1 WHERE Value = @NameValue

    PRINT @Name +' '+ Cast(@Value AS VARCHAR)
    SET @InnerLoopCount = @InnerLoopCount + 1
    END
    SET @LoopCounter = @LoopCounter + 1
    SET @InnerLoopCount=1;
    END

    ReplyDelete
  4. DECLARE @Count INT;

    CREATE TABLE #Table1
    (
    Name VARCHAR(50),
    Value INT
    )

    INSERT INTO #Table1 VALUES('Aryan',4),('Amit',3),('Neeraj',2),('Kapil',5)

    SELECT * FROM #Table1

    SET @Count = (SELECT MAX(Value) FROM #Table1);

    with t as (select 1 x
    union all
    select x + 1
    from t
    where x < @Count)
    SELECT Name, Value
    from t
    INNER JOIN #Table1
    ON x <= Value
    ORDER BY Name, x

    DROP TABLE #Table1

    ReplyDelete
  5. CREATE TABLE #Table1
    (
    Name VARCHAR(50),
    Value INT
    )

    INSERT INTO #Table1 VALUES('Aryan',4),('Amit',3),('Neeraj',2),('Kapil',5)

    SELECT #Table1.Name, Value
    FROM #Table1
    INNER JOIN master.dbo.spt_values t ON t.type='P'
    AND t.number BETWEEN 1 AND Value

    DROP TABLE #Table1

    ReplyDelete
  6. SELECT a.name, a.ntimes
    FROM RepeatN a
    Inner Join
    master.dbo.spt_values b on b.type = 'P' and b.number < a.Ntimes

    ReplyDelete

emo-but-icon

Follow Us

Recent

Comments

Donate

Side Ads

Text Widget

Connect Us

Sql Server 2017 Interview Questions
Sql Server Basic Interview Query Set-1
Sql Server Basic Interview Query Set-2
Sql Server Date-Time Interview Query SET-3
Sql Server Salary Interview Query SET-4
Sql Server Group By Interview Query SET-5
Sql Server Join Interview Query SET-6
Sql Server Tricky Join Interview Query SET-7
Sql Server DDL Interview Query SET-8
Small but very Tricky Sql Server Query SET-9
Very Much Tricky Query (not 4 fresher)SET-10
Sql Server Complex Interview Query SET-11
Sql Server Datatype Interview Questions
Sql Server View Interview Questions
Sql Server Index Interview Questions
TCS Tricky Sql Interview Queries
HCL Sql Interview Queries
Sql Server SP Interview Questions
Sql Server Trigger Interview Questions
Sql Server Temp Table Interview Questions
Sql Server 2016 Interview Questions
Sql Server Performance Tuning Interview Q.
Sql Server Constraints Interview Questions
Sql Server Storage/Size Interview Questions
Sql Server Very Basic Interview Questions
Sql Server Quiz for Fresher
Sql Server Icon Quiz for All
Sql Server 300+ Theoretical Interview Questions
Sql Server Complete Set

ASP.NET AND JQUERY INTERVIEW QUESTION
ASP.NET: Web Config Interview Question
ASP.NET: View State Interview Question
ASP.NET: Session Interview Question
ASP.NET: Session Interview Question 
ASP.NET: Security Interview Question
ASP.NET: Catching Interview Question
C# OOPS Interview Question
MORE Interview Question
JQUERY: Interview Question Set-1
JQUERY: Interview Question Set-2
JAVA-SCRIPT: Interview Question Set-1
JAVA-SCRIPT: Interview Question Set-2
ASP.NET MVC: Interview Question Set-1

POPULAR POSTS
10 MOST DIFFICULT INTERVIEW QUESTIONS
5 BEST INTERVIEW TIPS
YOUR 10 MISTAKE DURING INTERVIEW
2016 TOP 20 GROUP DISCUSSION TOPICS
HOW SQL PERFORMANCE AFFECTED BY DATATYPE
item