我有一个传统产品，我有维护。 一表中的有些类似于下面的示例：
DECLARE @t TABLE
(
id INT,
DATA NVARCHAR(30)
);
INSERT INTO @t
SELECT 1,
'name: Jim Ey'
UNION ALL
SELECT 2,
'age: 43'
UNION ALL
SELECT 3,
'----------------'
UNION ALL
SELECT 4,
'name: Johnson Dom'
UNION ALL
SELECT 5,
'age: 34'
UNION ALL
SELECT 6,
'----------------'
UNION ALL
SELECT 7,
'name: Jason Thwe'
UNION ALL
SELECT 8,
'age: 22'
SELECT *
FROM @t;
/*
You will get the following result
id DATA
----------- ------------------------------
1 name: Jim Ey
2 age: 43
3 ----------------
4 name: Johnson Dom
5 age: 34
6 ----------------
7 name: Jason Thwe
8 age: 22
*/
现在，我想在下面的表格中的信息：
name age
-------------- --------
Jim Ey 43
Johnson Dom 34
Jason Thwe 22
什么是最简单的方法是什么？ 谢谢。
出（略病态）好奇，我想拿出转化您所提供的精确输入数据的方式。
好得多，当然是正确建构原始数据。 与传统的系统，这是不可能的，但ETL过程可以创造，把这个信息到一个中间位置，使得这样一个丑陋的查询将不需要实时运行。
实施例＃1
这个例子假定所有ID是一致的，连续的（否则，额外的ROW_NUMBER()的列或一个新的身份列需要被用来保证正确的ID其余操作）。
SELECT
Name = REPLACE( Name, 'name: ', '' ),
Age = REPLACE( Age, 'age: ', '' )
FROM
(
SELECT
Name = T2.Data,
Age = T1.Data,
RowNumber = ROW_NUMBER() OVER( ORDER BY T1.Id ASC )
FROM @t T1
INNER JOIN @t T2 ON T1.id = T2.id +1 -- offset by one to combine two rows
WHERE T1.id % 3 != 0 -- skip delimiter records
) Q1
-- skip every other record (minus delimiters, which have already been stripped)
WHERE RowNumber % 2 != 0
例2：上顺序ID不存在依赖关系
这是一个更实际的例子，因为实际的ID值并不重要，仅排顺序。
DECLARE @NumberedData TABLE( RowNumber INT, Data VARCHAR( 100 ) );
INSERT @NumberedData( RowNumber, Data )
SELECT
RowNumber = ROW_NUMBER() OVER( ORDER BY id ASC ),
Data
FROM @t;
SELECT
Name = REPLACE( N2.Data, 'name: ', '' ),
Age = REPLACE( N1.Data, 'age: ', '' )
FROM @NumberedData N1
INNER JOIN @NumberedData N2 ON N1.RowNumber = N2.RowNumber + 1
WHERE ( N1.RowNumber % 3 ) = 2;
DELETE @NumberedData;
实施例＃3：光标
再次，这将是最好的，以避免运行实时这样的查询和使用计划，事务ETL过程。 根据我的经验，这样的半结构化数据很容易出现异常。
而实施例＃1和＃2（以及由他人提供的解决方案）表明与该数据的工作的聪明的方式，将这一数据的更实际的方法是一个游标。 为什么？ 它实际上可能有更好的表现（没有嵌套查询，递归，旋转，或行编号），甚至如果它是慢它用于错误处理提供更好的机会。
-- this could be a table variable, temp table, or staging table
DECLARE @Results TABLE ( Name VARCHAR( 100 ), Age INT );
DECLARE @Index INT = 0, @Data VARCHAR( 100 ), @Name VARCHAR( 100 ), @Age INT;
DECLARE Person_Cursor CURSOR FOR SELECT Data FROM @t;
OPEN Person_Cursor;
FETCH NEXT FROM Person_Cursor INTO @Data;
WHILE( 1 = 1 )BEGIN -- busy loop so we can handle the iteration following completion
IF( @Index = 2 ) BEGIN
INSERT @Results( Name, Age ) VALUES( @Name, @Age );
SET @Index = 0;
END
ELSE BEGIN
-- optional: examine @Data for integrity
IF( @Index = 0 ) SET @Name = REPLACE( @Data, 'name: ', '' );
IF( @Index = 1 ) SET @Age = CAST( REPLACE( @Data, 'age: ', '' ) AS INT );
SET @Index = @Index + 1;
END
-- optional: examine @Index to see that there are no superfluous trailing
-- rows or rows omitted at the end.
IF( @@FETCH_STATUS != 0 ) BREAK;
FETCH NEXT FROM Person_Cursor INTO @Data;
END
CLOSE Person_Cursor;
DEALLOCATE Person_Cursor;
性能
我创建的100K行样本源数据和上述三个例子似乎大致相当于用于变换数据。
我创建了一个百万行源数据和类似于以下的查询给出选择行的子集（如将在网页或报表上的网格使用）的优异性能。
-- INT IDENTITY( 1, 1 ) numbers the rows for us
DECLARE @NumberedData TABLE( RowNumber INT IDENTITY( 1, 1 ), Data VARCHAR( 100 ) );
-- subset selection; ordering/filtering can be done here but it will need to preserve
-- the original 3 rows-per-result structure and it will impact performance
INSERT @NumberedData( Data )
SELECT TOP 1000 Data FROM @t;
SELECT
N1.RowNumber,
Name = REPLACE( N2.Data, 'name: ', '' ),
Age = REPLACE( N1.Data, 'age: ', '' )
FROM @NumberedData N1
INNER JOIN @NumberedData N2 ON N1.RowNumber = N2.RowNumber + 1
WHERE ( N1.RowNumber % 3 ) = 2;
DELETE @NumberedData;
我看到4-10ms（i7-3960x）针对一组一百万条记录的执行时间。
鉴于表，你可以这样做：
;WITH DATA
AS
(
SELECT
SUBSTRING(t.DATA,CHARINDEX(':',t.DATA)+2,LEN(t.DATA)) AS value,
SUBSTRING(t.DATA,0,CHARINDEX(':',t.DATA)) AS ValueType,
ID,
ROW_NUMBER() OVER(ORDER BY ID) AS RowNbr
FROM
@t AS t
WHERE
NOT t.DATA='----------------'
)
, RecursiveCTE
AS
(
SELECT
Data.RowNbr,
Data.value,
Data.ValueType,
NEWID() AS ID
FROM
Data
WHERE
Data.RowNbr=1
UNION ALL
SELECT
Data.RowNbr,
Data.value,
Data.ValueType,
CASE
WHEN Data.ValueType='age'
THEN RecursiveCTE.ID
ELSE NEWID()
END AS ID
FROM
Data
JOIN RecursiveCTE
ON RecursiveCTE.RowNbr+1=Data.RowNbr
)
SELECT
pvt.name,
pvt.age
FROM
(
SELECT
ID,
value,
ValueType
FROM
RecursiveCTE
) AS SourceTable
PIVOT
(
MAX(Value)
FOR ValueType IN ([name],[age])
) AS pvt
产量
Name Age
------------------
Jim Ey 43
Jason Thwe 22
Johnson Dom 34
无需自连接，递归和以单道次在从行一个解决方案@t ：
SELECT *
FROM
(
SELECT
CASE
WHEN a.DATA LIKE 'name:%' THEN 'Name'
ELSE 'Age'
END AS Attribute,
CASE
WHEN a.DATA LIKE 'name:%' THEN SUBSTRING(a.DATA, 7, 4000) --or LTRIM(SUBSTRING(...,6,...))
ELSE SUBSTRING(a.DATA, 6, 4000) --or LTRIM(SUBSTRING(...,5,...))
END AS Value,
(ROW_NUMBER() OVER(ORDER BY id) + 1) / 2 AS PseudoDenseRank
FROM @t a
WHERE a.DATA LIKE 'name:%' OR a.DATA LIKE 'age:%'
) b
PIVOT( MAX(b.Value) FOR b.Attribute IN ([Name], [Age]) ) pvt
结果：
PseudoDenseRank Name Age
--------------- ----------- ---
1 Jim Ey 43
2 Johnson Dom 34
3 Jason Thwe 22
注1：派生表b意愿组name:%和age:%的行使用(ROW_NUMBER() OVER(ORDER BY id) + 1) / 2 。 结果派生表b ：
Attribute Value ROW_NUMBER() OVER(ORDER BY id) PseudoDenseRank
--------- ----------- ------------------------------ ---------------
Name Jim Ey 1 1
Age 43 2 1
Name Johnson Dom 3 2
Age 34 4 2
Name Jason Thwe 5 3
Age 22 6 3
注2： 如果从值id列没有间隙 （EX（ID 1，名称：吉姆EY），（ID 3岁：43）），那么你可以使用(a.id + 1) / 2 AS PseudoDenseRank代替(ROW_NUMBER() OVER(ORDER BY id) + 1) / 2 AS PseudoDenseRank 。
注3： 如果使用(a.id + 1) / 2 AS PseudoDenseRank溶液 （以组名和年龄的行），那么值的第一ID应该是一个奇数。 如果值第一个ID是偶数，那么你应该用这句话a.id / 2 AS PseudoDenseRank 。
这里还有一个选项，如果你升级到SQL Server 2012，它实现了聚合函数OVER子句。 这种方法可以让你只选择那些你知道你想要的标签，也不管有多少行是名之间找到它们。
这也将工作，如果姓名和年龄并不总是在一组代表一个人的行内的顺序相同。
with Ready2Pivot(tag,val,part) as (
select
CASE WHEN DATA like '_%:%' THEN SUBSTRING(DATA,1,CHARINDEX(':',DATA)-1) END as tag,
CASE WHEN DATA like '_%:%' THEN SUBSTRING(DATA,CHARINDEX(':',DATA)+1,8000) END as val,
max(id * CASE WHEN DATA LIKE 'name:%' THEN 1 ELSE 0 END)
over (
order by id
)
from @t
where DATA like '_%:%'
)
select [name], [age]
from Ready2Pivot
pivot (
max(val)
for tag in ([name], [age])
) as p
如果你的旧数据有额外的项目条目（说“altName是：吉米”），这个查询会忽略它。 如果你的旧数据对别人的年龄没有行（无身份证号码），它会给你在那个地方NULL。 “...名称”作为数据，因此重要的是，每个组行有一个将与最接近的前行中的所有信息关联“的名字：...”行。
