I have an Input table as under

```
Id  Data
1   Column1: Value1
2   Column2: Value11
3   Column3: Value111
4   Column1: Value2
5   Column2: Value22
6   Column3: Value222

```

I am looking for an output as under

```
Column1      Column2    Column3
Value1       Value11    Value111
Value2       Value22    Value222

```

How can I achieve so? It could have been done easily by using a WHILE LOOP and by a bit of mathematical logic, but I am looking for a more optimized one if possible by only SELECT queries (no LOOPS).

I have tried also by splitting using (':') as delimiter and then transforming ROWS to COLUMNS (PIVOT) but somewhat could not be able to proceed. (That's my thought, peoples may have more better thoughts).

My shot so far

```
Declare @t table(Id int identity(1,1),Data varchar(1000))
Insert into @t Values
    ('Column1: Value1'),('Column2: Value11'),('Column3: Value111')
    ,('Column1: Value2'),('Column2: Value22'),('Column3: Value222')

Select *
FROM @t
SELECT 
 F1.id,
 F1.Data,
 O.splitdata 
FROM
 (
 SELECT *,
 cast('<X>'+replace(F.Data,':','</X><X>')+'</X>' as XML) as xmlfilter from @t F
 )F1
 CROSS APPLY
 ( 
 SELECT fdata.D.value('.','varchar(50)') as splitdata 
 FROM f1.xmlfilter.nodes('X') as fdata(D)) O

```