▼

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



-

8 comments:

  1. Unknown23 December 2017 at 20:41

    This comment has been removed by the author.

    ReplyDelete
  2. Unknown23 December 2017 at 20:41

    This comment has been removed by the author.

    ReplyDelete
  3. Unknown23 December 2017 at 20:42

    This comment has been removed by the author.

    ReplyDelete
  4. Unknown23 December 2017 at 20:43

    This comment has been removed by the author.

    ReplyDelete
  5. Unknown23 December 2017 at 20:43

    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
  6. sanjusoftwareeng5 May 2018 at 19:01

    good

    ReplyDelete
  7. Anonymous22 August 2018 at 18:13

    Great! It sounds good. Thanks for sharing.. https://view.ly/v/W7bQdazBruOw

    ReplyDelete