daily pastebin goal
51%
SHARE
TWEET

Untitled

a guest May 16th, 2018 90 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Declare @txt  nvarchar(max)='2450,10,54,kb2344,kd5433;87766,500,100,ki5332108,ow092827'
  2.  
  3. Select ID=A.RetSeq
  4.       ,B.*
  5.  From (
  6.         Select RetSeq = Row_Number() over (Order By (Select null))
  7.               ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
  8.         From  (Select x = Cast('<x>' + replace(@txt ,';','</x><x>')+'</x>' as xml).query('.')) as A
  9.         Cross Apply x.nodes('x') AS B(i)
  10.       ) A
  11.  Cross Apply (
  12.                 Select Val1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
  13.                       ,Val2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
  14.                       ,Val3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
  15.                       ,Val4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
  16.                       ,Val5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
  17.                 From  (Select Cast('<x>' + replace(A.RetVal,',','</x><x>')+'</x>' as xml) as xDim) as B1
  18.              ) B
  19.    
  20. ID  Val1    Val2    Val3    Val4        Val5
  21. 1   2450    10      54      kb2344      kd5433
  22. 2   87766   500     100     ki5332108   ow092827
RAW Paste Data
Top