martes, 29 de mayo de 2018

SQL Comma separated string to rows and columns

DECLARE @Data NVARCHAR(MAX) = 'Jessica Espinosa, Gerente de Nomina, REG. PAT. L0165468104, R.F.C. WKA160429DD8'
DECLARE @Delimiter NVARCHAR(5) = ','
DECLARE @Table TABLE ( Data NVARCHAR(MAX), SequentialOrder INT IDENTITY(1, 1))

DECLARE @TextXml XML;
SELECT @TextXml = CAST('<d>' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Data, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'), '"', '&quot;'), '''', '&apos;'), @Delimiter, '</d><d>') + '</d>' AS XML);

INSERT INTO @Table (Data)
SELECT Data = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM(T.split.value('.', 'nvarchar(max)'))), '&amp;', '&'), '&lt;', '<'), '&gt;', '>'), '&quot;', '"'), '&apos;', '''')
FROM @TextXml.nodes('/d') T(Split)

SELECT
(SELECT Data FROM @Table WHERE SequentialOrder=1) [Signature1],
(SELECT Data FROM @Table WHERE SequentialOrder=2) [Signature2],
(SELECT Data FROM @Table WHERE SequentialOrder=3) [Signature3],
(SELECT Data FROM @Table WHERE SequentialOrder=4) [Signature4]

SELECT * FROM @Table