Tuesday, 4 July 2017

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)



WHAT OTHERS ARE READING/RELATED TO THIS:

5 comments:

  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