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
  6. Great! It sounds good. Thanks for sharing.. https://view.ly/v/W7bQdazBruOw

    ReplyDelete

Upload valid file in C#

    protected bool CheckFileExtandLength(HttpPostedFile HtmlDocFile)     {         try         {             Dictionary<string, byte[]>...