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

Bulk SMS Sender ID Codes

Bulk SMS Sender ID Codes Following table is of Telecom Operator Code. Provider Code Airtel A BSNL B Datacom Solu...