¿Cómo cambiar la información en esta tabla a una forma fácil de usar?

Tengo un producto henetworkingado que tengo que mantener. Una de las tablas es algo similar al siguiente ejemplo:

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 */ 

Ahora quiero get la información en la siguiente forma:

 name age -------------- -------- Jim Ey 43 Johnson Dom 34 Jason Thwe 22 

¿Cuál es la forma más fácil de hacer esto? Gracias.

Por curiosidad (un tanto morbosa) traté de encontrar un medio para transformar los datos de input exactos que me has proporcionado.

Mucho mejor, por supuesto, sería estructurar adecuadamente los datos originales. Con un sistema henetworkingado, esto puede no ser posible, pero se podría crear un process ETL para llevar esta información a una location intermedia, de modo que no sea necesario ejecutar una consulta desagradable como esta en time real.

Ejemplo 1

Este ejemplo asume que todos los ID son coherentes y secuenciales (de lo contrario, se necesitaría usar una columna ROW_NUMBER() adicional o una nueva columna de identidad para garantizar el correcto funcionamiento de las restantes en el 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 

Ejemplo n. ° 2: sin dependencia de identificadores secuenciales

Este es un ejemplo más práctico porque los valores de ID reales no importan, solo la secuencia de fila.

 DECLARE @NumbenetworkingData TABLE( RowNumber INT, Data VARCHAR( 100 ) ); INSERT @NumbenetworkingData( 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 @NumbenetworkingData N1 INNER JOIN @NumbenetworkingData N2 ON N1.RowNumber = N2.RowNumber + 1 WHERE ( N1.RowNumber % 3 ) = 2; DELETE @NumbenetworkingData; 

Ejemplo n. ° 3: Cursor

Una vez más, sería mejor evitar ejecutar una consulta como esta en time real y usar un process de ETL transaccional progtwigdo. En mi experiencia, los datos semiestructurados como este son propensos a anomalías.

Mientras que los ejemplos n. ° 1 y n. ° 2 (y las soluciones proporcionadas por otros) demuestran forms ingeniosas de trabajar con los datos, una forma más práctica de transformar estos datos sería un cursor. ¿Por qué? en realidad puede tener un mejor performance (sin consultas anidadas, recursión, pivoteo o numeración de filas) e incluso si es más lento, ofrece muchas mejores oportunidades para el event handling errores.

 -- 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; 

Actuación

Creé datos de origen de muestra de 100K filas y los tres ejemplos mencionados anteriormente parecen más o less equivalentes para transformar datos.

Creé un millón de filas de datos fuente y una consulta similar a la siguiente ofrece un excelente performance para seleccionar un subset de filas (como las que se usarían en una cuadrícula en una página web o en un informe).

 -- INT IDENTITY( 1, 1 ) numbers the rows for us DECLARE @NumbenetworkingData 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 @NumbenetworkingData( Data ) SELECT TOP 1000 Data FROM @t; SELECT N1.RowNumber, Name = REPLACE( N2.Data, 'name: ', '' ), Age = REPLACE( N1.Data, 'age: ', '' ) FROM @NumbenetworkingData N1 INNER JOIN @NumbenetworkingData N2 ON N1.RowNumber = N2.RowNumber + 1 WHERE ( N1.RowNumber % 3 ) = 2; DELETE @NumbenetworkingData; 

Estoy viendo times de ejecución de 4-10ms (i7-3960x) contra un set de un millón de loggings.

Dada esa tabla, puedes hacer esto:

 ;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 

Salida

 Name Age ------------------ Jim Ey 43 Jason Thwe 22 Johnson Dom 34 

Una solución sin autouniones, recursión y con una sola pasada sobre las filas de @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 @ta WHERE a.DATA LIKE 'name:%' OR a.DATA LIKE 'age:%' ) b PIVOT( MAX(b.Value) FOR b.Attribute IN ([Name], [Age]) ) pvt 

Resultados:

 PseudoDenseRank Name Age --------------- ----------- --- 1 Jim Ey 43 2 Johnson Dom 34 3 Jason Thwe 22 

Nota 1: la tabla derivada b (ROW_NUMBER() OVER(ORDER BY id) + 1) / 2 name:% y age:% filas usando (ROW_NUMBER() OVER(ORDER BY id) + 1) / 2 . Resultados para la tabla derivada 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 

Nota 2: si los valores de la columna de id no tienen espacios (por ejemplo, (id 1, nombre: Jim Ey), (id 3 edad: 43)), entonces puede usar (a.id + 1) / 2 AS PseudoDenseRank en lugar de (ROW_NUMBER() OVER(ORDER BY id) + 1) / 2 AS PseudoDenseRank .

Nota 3: si usa (a.id + 1) / 2 AS PseudoDenseRank solución (a.id + 1) / 2 AS PseudoDenseRank (para las filas de nombre y edad del grupo), entonces el primer valor de identificación debe ser un número impar. Si el primer valor de identificación es un número par, entonces debe usar esta expresión a.id / 2 AS PseudoDenseRank .

Aquí hay otra opción si actualiza a SQL Server 2012, que implementa la cláusula OVER para funciones agregadas. Este enfoque le permitirá elegir solo aquellas tags que sabe que desea y searchlas, independientemente de la cantidad de filas que haya entre los nombres.

Esto también funcionará si los nombres y las edades no están siempre en el mismo order dentro de un grupo de filas que representan una sola persona.

 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 

Si sus datos henetworkingados tienen una input con elementos adicionales (digamos "altName: Jimmy"), esta consulta la ignorará. Si sus datos henetworkingados no tienen fila (ni número de identificación) para la edad de alguien, le dará NULO en ese punto. Asociará toda la información con la fila anterior más cercana con "nombre: …" como los DATOS, por lo que es importante que cada grupo de filas tenga una fila de "nombre: …".