czwartek, 30 stycznia 2014

Konwersja tekstu oddzielonego separatorem na tabelę.

Dzisiejszy post został zainspirowany tematem z forum wss.geekclub.pl. Zgłoszony problem dotyczył rozbicia tekstu, oddzielonego separatorem na osobne wiersze.

Mając dane w następujacym formacie:

NumerListu    Data    Dokumenty
3473919441    2014-01-05    WZ-71, FV-17/14, FV-18/14, FV-99/14, FV-102/14
3473919581    2014-01-07    WZ-33, FV-14/14
3473919615    2014-01-07    WZ-74, FV-19/14
3473919946    2014-01-11    WZ-27, FV-24/14, FV-25/14

Chcemy je zamienić na coś takiego

NumerListu    Data    DokumentyWZ    DokumentyFV
3473919441    2014-01-05    WZ-71    FV-17/14
3473919441    2014-01-05    WZ-71    FV-18/14
3473919441    2014-01-05    WZ-71    FV-99/14
3473919441    2014-01-05    WZ-71    FV-102/14
3473919581    2014-01-07    WZ-33    FV-14/14
3473919615    2014-01-07    WZ-74    FV-19/14
3473919946    2014-01-11    WZ-27    FV-24/14
3473919946    2014-01-11    WZ-27    FV-25/14

Swego czasu zamianę CSV na postać tabularyczną implementowałem ze pomocą pętli while i tablicy tymczasowej. Jednakże od SQL Server 2005 można to zadanie rozwiązać między innymi korzystając z xml.

Poniżej kod z rozwiązaniem zadania.

declare @table table (
    numerlistu varchar(20),
    data date,
    dokumenty varchar(200)
)

insert into @table
select '3473919441', '2014-01-05', 'WZ-71, FV-17/14, FV-18/14, FV-99/14, FV-102/14' union all
select '3473919581', '2014-01-07', 'WZ-33, FV-14/14' union all
select '3473919615', '2014-01-07', 'WZ-74, FV-19/14' union all
select '3473919946', '2014-01-11', 'WZ-27, FV-24/14, FV-25/14'

;with cte as (
select
NumerListu,
Data,
substring(t.dokumenty, 1,charindex(',', t.dokumenty,0)-1) as DokumentyWZ,
cast('' + replace(substring(t.dokumenty, charindex(',', t.dokumenty,0)+1, len(t.dokumenty)), ',', '') + '' as xml) as DokumentyFK
from @table t
)

select
NumerListu,
Data,
DokumentyWZ,
dokumenty.dokument.value('.', 'varchar(100)') as DokumentyFK
from cte
cross apply DokumentyFK.nodes('/items/item') as dokumenty(dokument)


Niech stanowi on taką ciekawostkę do czego można wykorzystać xml w SQL Server. Nie zawsze tego typu zastosowania są oczywiste.