Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 разбить колонку  [new]
lgdmitry
Member

Откуда: Energodar
Сообщений: 48
Есть у меня таблица с одним полем varchar(200). С такими данными

D9816!20120420135049524!140!OUTBOUND_REPLY!!EILCHEN!20120428!i

есть такой sql запрос который разобьет на колонки, где после каждого '!' новая колонка

D9816 20120420135049524 140 OUTBOUND_REPLY EILCHEN 20120428 i


за ранее спасибо!
8 июн 12, 18:04    [12690050]     Ответить | Цитировать Сообщить модератору
 Re: разбить колонку  [new]
Alexandr Kr.
Member

Откуда: Украина, Харьков
Сообщений: 165
lgdmitry

есть такой sql запрос который разобьет на колонки, где после каждого '!' новая колонка



А какое конечное количество колонок? 200/2=100 колонок?
8 июн 12, 18:25    [12690181]     Ответить | Цитировать Сообщить модератору
 Re: разбить колонку  [new]
lgdmitry
Member

Откуда: Energodar
Сообщений: 48
до 20 колонок
8 июн 12, 18:26    [12690191]     Ответить | Цитировать Сообщить модератору
 Re: разбить колонку  [new]
Baddy
Member

Откуда: Харьков
Сообщений: 174
lgdmitry,

если кол-во разделителей фиксированное, то можешь сначала выгрузить табличку в файл (с помощью BCP например), а потом из файла обратно в таблицу с нужным кол-вом полей (Bulk Insert)
8 июн 12, 18:35    [12690230]     Ответить | Цитировать Сообщить модератору
 Re: разбить колонку  [new]
Alexandr Kr.
Member

Откуда: Украина, Харьков
Сообщений: 165
lgdmitry,

declare @str varchar(200)= 'D9816!20120420135049524!140!OUTBOUND_REPLY!!EILCHEN!20120428!i';
with    cte
          as ( select   left(@str, charindex('!', @str) - 1) as Col ,
                        substring(@str, charindex('!', @str) + 1, 200) as rest ,
                        1 as Level
               union all
               select   left(rest,
                             isnull(nullif(charindex('!', rest), 0), 200) - 1) ,
                        substring(rest,
                                  isnull(nullif(charindex('!', rest), 0), 200)
                                  + 1, 200) ,
                        Level + 1
               from     cte
               where    len(rest) > 0
             )
    select  [1] ,
            [2] ,
            [3] ,
            [4] ,
            [5] ,
            [6] ,
            [7] ,
            [8] ,
            [9] ,
            [10] ,
            [11] ,
            [12] ,
            [13] ,
            [14] ,
            [15] ,
            [16] ,
            [17] ,
            [18] ,
            [19] ,
            [20]
    from    ( select    Col ,
                        Level
              from      cte
            ) as src pivot 
( max(col) for Level in ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
                          [11], [12], [13], [14], [15], [16], [17], [18], [19],
                          [20] ) )
as pvt;
8 июн 12, 18:36    [12690239]     Ответить | Цитировать Сообщить модератору
 Re: разбить колонку  [new]
lgdmitry
Member

Откуда: Energodar
Сообщений: 48
Alexandr Kr.,

Честно говоря мало что понятно из этого, мне нужны все колонки, а не 1, с charindex я уже игрался. Я думал что может есть универсальный select как в bulk insert, где можно задать разделитель и все.
8 июн 12, 18:56    [12690313]     Ответить | Цитировать Сообщить модератору
 Re: разбить колонку  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
lgdmitry, а вы скрипт запускали вообще?
8 июн 12, 19:05    [12690348]     Ответить | Цитировать Сообщить модератору
 Re: разбить колонку  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Baddy
сначала выгрузить табличку в файл (с помощью BCP например), а потом из файла обратно в таблицу с нужным кол-вом полей (Bulk Insert)
+1
8 июн 12, 19:14    [12690378]     Ответить | Цитировать Сообщить модератору
 Re: разбить колонку  [new]
lgdmitry
Member

Откуда: Energodar
Сообщений: 48
Alexandr Kr.
lgdmitry,

declare @str varchar(200)= 'D9816!20120420135049524!140!OUTBOUND_REPLY!!EILCHEN!20120428!i';
with    cte
          as ( select   left(@str, charindex('!', @str) - 1) as Col ,
                        substring(@str, charindex('!', @str) + 1, 200) as rest ,
                        1 as Level
               union all
               select   left(rest,
                             isnull(nullif(charindex('!', rest), 0), 200) - 1) ,
                        substring(rest,
                                  isnull(nullif(charindex('!', rest), 0), 200)
                                  + 1, 200) ,
                        Level + 1
               from     cte
               where    len(rest) > 0
             )
    select  [1] ,
            [2] ,
            [3] ,
            [4] ,
            [5] ,
            [6] ,
            [7] ,
            [8] ,
            [9] ,
            [10] ,
            [11] ,
            [12] ,
            [13] ,
            [14] ,
            [15] ,
            [16] ,
            [17] ,
            [18] ,
            [19] ,
            [20]
    from    ( select    Col ,
                        Level
              from      cte
            ) as src pivot 
( max(col) for Level in ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
                          [11], [12], [13], [14], [15], [16], [17], [18], [19],
                          [20] ) )
as pvt;


как мне его переделать, чтобы данные брались из таблицы ?
8 июн 12, 19:27    [12690427]     Ответить | Цитировать Сообщить модератору
 Re: разбить колонку  [new]
qwerty112
Guest
declare @t table (id int identity, txt varchar(200)) 

insert into @t (txt)
select 'D9816!20120420135049524!140!OUTBOUND_REPLY!!EILCHEN!20120428!i' union all
select 'a9816!10120420135049524!140!OUTBOUND_REPLY!!EILCHEN!20120428!i'

--select id, 
--cast('<row><a>'+replace(txt, '!', '</a><a>')+'</a></row>' as xml) as [txt]
--from @t

;with cte as
(select id, 
cast('<row><a>'+replace(txt, '!', '</a><a>')+'</a></row>' as xml) as [txt]
from @t)

select id, [1], [2], [3], [4], [5], [6], [7], [8] 
from

(select t1.id, t2.[val], row_number() over(partition by t1.id order by t1.id) as rn 
from cte t1
cross apply 
	(select t.c.value('text()[1]', 'varchar(100)') as [val]
	from t1.txt.nodes('/row/a') as t(c))as t2) a

pivot (min(a.[val]) for rn in ([1], [2], [3], [4], [5], [6], [7], [8])) pvt

(2 row(s) affected)
id          1                    2                    3                    4                    5                    6                    7                    8
----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
1           D9816                20120420135049524    140                  OUTBOUND_REPLY       NULL                 EILCHEN              20120428             i
2           a9816                10120420135049524    140                  OUTBOUND_REPLY       NULL                 EILCHEN              20120428             i

(2 row(s) affected)
8 июн 12, 19:46    [12690515]     Ответить | Цитировать Сообщить модератору
 Re: разбить колонку  [new]
lgdmitry
Member

Откуда: Energodar
Сообщений: 48
qwerty112,

Спасибо! То что надо!
8 июн 12, 20:41    [12690638]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить