Thursday, 21 December 2017

Repeat Rows N Times According to Column Value in SQL Server

CREATE TAblE #temp
(
T_Name      VARCHAR(50),
T_Times      BIGINT
)

INSERT INTO #temp(T_Name,T_Times) VALUES ('ASHISH',4)
INSERT INTO #temp(T_Name,T_Times) VALUES ('PANKAJ',3)
INSERT INTO #temp(T_Name,T_Times) VALUES ('RUPESH',2)
INSERT INTO #temp(T_Name,T_Times) VALUES ('MANISH',5)

SELECT t.T_Name ,t.T_Times FROM
(SELECT  T_Name,T_Times,CAST(('<val>'+REPLICATE(T_Name+'</val><val>',T_Times-1)
+'</val>') AS XML )AS X FROM #temp)t CROSS APPLY t.X.nodes('/val')y(z)

drop table #temp



7 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. How to remove additional .val> generated by XML for every row due to

    SELECT Name,Times,
    CAST((''+REPLICATE(Name+'',Times-1)+'.val>') AS XML ) AS X
    FROM #temp

    23 December 2017 at 20:41

    ReplyDelete

Track your Facebook login attempt

Facebook provides a feature which allows you to see all active sessions from different devices and apps together with the date it was last...