Side Gadgets

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 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)


8 comments
Share
Labels, ,

Comments

  1. Vikas4 July 2017 at 23:21

    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. Vikas11 July 2017 at 23:13

    --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. Vikas14 July 2017 at 23:43

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

      Delete
    2. Vikas19 July 2017 at 22:53

      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. Neeraj13 July 2017 at 02:18

    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. Anonymous9 August 2017 at 20:34

    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. Anonymous9 August 2017 at 20:53

    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. Unknown23 November 2017 at 02:05

    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

Contact Form

Send