Отличная статья от Алексея Князева — тут
И так, условие задачи:
| Есть примерно такая табличка: | Из нее нужно получить таблицу такого плана: | ||||||||||||||||||||||||||
|
|
Для начала всем рекомендую прочитать статью Массивы и Списки в SQL Server
Cоздадим тестовый набор данных:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
declare @t table (name varchar(255)) insert into @t select 'Киев' union all select 'Киев Моссква' union all select 'Киев Моссква Киев' union all select 'Киев Моссква Киев Киев Моссква Киев' union all select 'Киев Моссква' union all select 'Киев Моссква Киев' |
Предлагаю на выбор 4 варианта решения подобных задач:
1. Используем динамический SQL-запрос:
|
1 2 3 4 5 |
declare @str varchar(max) set @str='' select @str=@str+' '+name from @t set @str='select '''+replace(ltrim(@str), ' ', ''' as City union all select ''')+'''' exec(@str) |
2. Используем табличное выражение (СТЕ):
|
1 2 3 4 5 6 7 8 9 10 |
;with cte(city, val) as ( select top 1 convert(varchar(max),''), (select name+' ' from @t for xml path('')) from @t union all select convert(varchar(max), substring(val, 1, charindex(' ', val))), stuff(val, 1, charindex(' ', val), '') from cte where val!='' ) select city from cte where city!='' |
3. XML (вариант с атрибутами):
|
1 2 3 4 5 6 7 8 9 |
select city=chs.ch.value('@i', 'varchar(200)') from ( select convert (xml,'') from @t ) a(r) cross apply r.nodes('ROOT/V') as chs(ch) |
4. XML (вариант через элементы):
|
1 2 3 4 5 6 |
select city=a.b.value('.', 'varchar(20)') from (select xml=convert(xml,''+replace( convert(varchar(max),(select name+' ' from @t for xml path(''))) , ' ', ' ')+' ')) t cross apply xml.nodes('/ROOT/C') a(b) |
