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

Top 10 Web Application Security Risks

Injection Injection flaws, such as SQL injection, LDAP injection, and CRLF injection, occur when an attacker sends untrusted data to an ...