Rank: Administration
Medals: Groups: Administrators
Joined: 10/20/2012
Posts: 106
Points: 327
Location: Tbilisi
Thanks: 0 times
Was thanked: 0 time(s) in 0 post(s)
|
ბევრი რომ არ იწვალო, შენი მონაცემებისთვის ამის გაკეთება ასეა შესაძლებელი : ქუოტა (Quote):
DECLARE @t TABLE ( [Val] VARCHAR(55) )
Insert into @t (val) values ('5 2 6 2 2 2 6 1 8 4 4 3 2 4 5')
Insert into @t (val) values ('1 2 1 3 3 6 4 3 4 2 2 1 5 2 6')
Insert into @t (val) values ('6 2 7 4 2 3 3 9 7 8 5 4 3 2 4')
Insert into @t (val) values ('6 4 3 4 2 2 2 2 2 6 7 5 4 6 3')
Insert into @t (val) values ('3 3 7 7 4 4 9 4 4 6 7 1 5 2 3')
Insert into @t (val) values ('9 2 2 2 3 6 9 3 5 7 1 3 4 7 2')
Insert into @t (val) values ('4 4 9 2 2 8 2 2 3 6 2 3 6 4 5')
Insert into @t (val) values ('8 2 6 3 6 4 3 4 4 2 2 5 2 7 7')
Insert into @t (val) values ('2 3 4 4 2 6 2 8 2 6 9 9 5 3 3')
Insert into @t (val) values ('2 2 2 2 4 4 5 5 4 3 3 7 3 3 2')
SELECT * FROM @t
;WITH ct AS
(
select ROW_NUMBER() over(Order by val) as id, val FROM @t
)
SELECT m.id,
m.val,
O.splitval
FROM
(
SELECT [ID],val,cast('<X>'+replace(F.val,' ','</X><X>')+'</X>' as XML) as xmlvalue from ct F
)m
CROSS APPLY
(
SELECT fval.D.value('.','nvarchar(50)') as splitval
FROM m.xmlvalue.nodes('X') as fval(D)) O
|
