CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
Ardi
Ardi

Reputation: 75

Change new line into New Record

I have record as shown below:

enter image description here

Is it possible to turn this record on SQL Server 2008 R2 into table below?

enter image description here

Picture 1 SiteName type is Varchar and Allocation is Varchar too. Every value has new line, i mean new line is char(13). the value could be '(0.0 - 12.0)' + CHAR(13) + '(15.0 - 18.0)' and so on.

Picture 2 this char(13) is a reference to break every line into new row on field allocation, and every row will inherit their SiteName

The new formatted table will be dump into new table(pic.2), so the original data(pic.1) is just for the reference.

I'm stuck to do this on SQL Server 2008, any suggestions would be very appreciated.

Upvotes: 0

Views: 56

Answers (1)

gotqn
gotqn

Reputation: 43666

You need to split the data like the example below (you can split by different symbols if CHAR(13) + CHAR(10) is not your new line separators:

DECLARE @DataSource TABLE
(
    [SiteName] NVARCHAR(128)
   ,[Allocation] NVARCHAR(MAX)
);

INSERT INTO @DataSource ([SiteName], [Allocation])
VALUES 
('a', '1
2')
,('b', '3
4
5')
,('c', '6
7');

WITH DataSource ([SiteName], [Allocations]) AS
(
    SELECT [SiteName]
          ,CAST('<a>' + REPLACE([Allocation], CHAR(13) + CHAR(10), '</a><a>') + '</a>' AS XML)
    FROM @DataSource DS
)
SELECT DS.SiteName
      ,LTRIM(RTRIM(T.c.value('.', 'nvarchar(128)')))
FROM DataSource DS
CROSS APPLY  DS.Allocations.nodes('./a') T(c);

Upvotes: 2

Related Questions