Split selection results (single column) into multiple columns

Question

I tried to figure out how to do this, but I think that I just do not have the skills to know what to look for in the first place. I work with an existing system, and I cannot change the database schema, nor can I determine how users enter data. I have to work with what we have.

Currently, our user places statistics data in one text field in the table. They use the standard format of one statue per line, with text identifiers, to “differentiate” the details of statistics. i.e.

<Category> - D:<Description> Q:<Quanitity> V:<Value> <Category> - D:<Description> Q:<Quanitity> V:<Value> 

(there are no brackets in the actual data> ... I just used them to show where the details go.)

In the above example, two statistics are stored in one record of a text field ... and in the table there are many records of these records.

Edit: I am using MS SQL Server 2005. I need to create a report for the quanitites / values ​​sums to describe.

I implemented a user separation function that I can use on one record to split each row into separate records ... but this is as far as I could.

I need to "Select statistics from StatsTable", then skip each statistics record, separate it into separate lines, then extract the category, description, quantity and value from each divided line, and then return all the results to one table.

Answer 1

I managed to copy the nested cursor ... It looks like it works.

 declare o CURSOR FAST_FORWARD FOR select comments from EVENT declare @comment nvarchar(max) OPEN o FETCH NEXT FROM o into @comment while @@FETCH_STATUS = 0 BEGIN Declare @item nvarchar(750) declare @tbl Table(Category nvarchar(250), Description nvarchar(250), Quantity nvarchar(250), Value DECIMAL(10,2)) declare c CURSOR FAST_FORWARD FOR SELECT items FROM dbo.Split(@comment, Char(10)) OPEN c FETCH NEXT FROM c into @item WHILE @@FETCH_STATUS = 0 BEGIN set @item = @item + ':' insert into @tbl Values (LTRIM(RTRIM(SUBSTRING(@item, 1, CHARINDEX(' - ', @item) - 1))), CASE when @item like '%D:%' Then LTRIM(RTRIM(SUBSTRING(@item, CHARINDEX('D:', @item) + 2, CHARINDEX(':', @item, CHARINDEX('D:', @item)+2) - CHARINDEX('D:', @item) - 3))) else '' end, CASE when @item like '%Q:%' Then LTRIM(RTRIM(SUBSTRING(@item, CHARINDEX('Q:', @item) + 2, CHARINDEX(':', @item, CHARINDEX('Q:', @item)+2) - CHARINDEX('Q:', @item) - 3))) else '1' end, CASE when @item like '%V:%' Then CONVERT(DECIMAL(10,2),dbo.RemoveNonNumericCharacters(LTRIM(RTRIM(SUBSTRING(@item, CHARINDEX('V:', @item) + 2, CHARINDEX(':', @item, CHARINDEX('V:', @item)+2) - CHARINDEX('V:', @item) - 3))))) else 0 end) FETCH NEXT FROM c into @item END CLOSE c DEALLOCATE c END CLOSE o DEALLOCATE o SELECT * FROM @tbl