multiples - pivot sin funcion de agregado sql server

SQL Server: columnas a filas

Question

Buscando una solución elegante (o cualquiera) para convertir columnas en filas.

Aquí hay un ejemplo: tengo una tabla con el siguiente esquema:

[ID] [EntityID] [Indicator1] [Indicator2] [Indicator3] ... [Indicator150]

Esto es lo que quiero obtener como resultado:

[ID] [EntityId] [IndicatorName] [IndicatorValue]

Y los valores de resultado serán:

1 1 ''Indicator1'' ''Value of Indicator 1 for entity 1''
2 1 ''Indicator2'' ''Value of Indicator 2 for entity 1''
3 1 ''Indicator3'' ''Value of Indicator 3 for entity 1''
4 2 ''Indicator1'' ''Value of Indicator 1 for entity 2''

Y así..

¿Esto tiene sentido? ¿Tiene alguna sugerencia sobre dónde buscar y cómo hacerlo en T-SQL?

Answer 1

Bueno, si tiene 150 columnas, entonces creo que UNPIVOT no es una opción. Entonces podrías usar el truco xml

;with CTE1 as (
    select ID, EntityID, (select t.* for xml raw(''row''), type) as Data
    from temp1 as t
), CTE2 as (
    select
         C.id, C.EntityID,
         F.C.value(''local-name(.)'', ''nvarchar(128)'') as IndicatorName,
         F.C.value(''.'', ''nvarchar(max)'') as IndicatorValue
    from CTE1 as c
        outer apply c.Data.nodes(''row/@*'') as F(C)
)
select * from CTE2 where IndicatorName like ''Indicator%''

demostración fiddle sql

También puede escribir SQL dinámico, pero me gusta xml más; para SQL dinámico, debe tener permisos para seleccionar datos directamente desde la tabla y eso no siempre es una opción.

ACTUALIZAR
Como hay una gran llama en los comentarios, creo que agregaré algunos pros y contras de xml / SQL dinámico. Intentaré ser tan objetivo como pueda y no mencionar la elegancia y la fealdad. Si tiene otros pros y contras, edite la respuesta o escriba en los comentarios

contras

• no es tan rápido como el SQL dinámico, las pruebas aproximadas me dieron que el xml es aproximadamente 2.5 veces más lento que el dinámico (era una consulta en la tabla de ~ 250000 filas, por lo que esta estimación no es exacta). Puede compararlo usted mismo si lo desea, aquí está el ejemplo de sqlfiddle , en 100000 filas, era de 29 (xml) frente a 14 (dinámico);
• puede ser que sea más difícil de entender para las personas que no están familiarizadas con xpath;

pros

• es el mismo alcance que sus otras consultas, y eso podría ser muy útil. Algunos ejemplos vienen a la mente
  • puede consultar tablas inserted y deleted dentro de su desencadenador (no es posible con dinámica en absoluto);
  • el usuario no tiene que tener permisos en la selección directa de la tabla. Lo que quiero decir es que si tiene una capa de procedimientos almacenados y el usuario tiene permisos para ejecutar sp, pero no tiene permisos para consultar tablas directamente, aún podría usar esta consulta dentro del procedimiento almacenado;
  • puede consultar la variable de tabla que ha rellenado en su ámbito (para pasarla dentro del SQL dinámico, debe convertirla en tabla temporal o crear un tipo y pasarla como un parámetro en SQL dinámico;
• puede hacer esta consulta dentro de la función (escalar o valor de tabla). No es posible usar SQL dinámico dentro de las funciones;

Answer 2

Necesitaba una solución para convertir columnas en filas en Microsoft SQL Server, sin conocer los nombres de las columnas (utilizados en el desencadenante) y sin sql dinámico (el sql dinámico es demasiado lento para usarlo en un desencadenador).

Finalmente encontré esta solución, que funciona bien:

SELECT
    insRowTbl.PK,
    insRowTbl.Username,
    attr.insRow.value(''local-name(.)'', ''nvarchar(128)'') as FieldName,
    attr.insRow.value(''.'', ''nvarchar(max)'') as FieldValue 
FROM ( Select      
          i.ID as PK,
          i.LastModifiedBy as Username,
          convert(xml, (select i.* for xml raw)) as insRowCol
       FROM inserted as i
     ) as insRowTbl
CROSS APPLY insRowTbl.insRowCol.nodes(''/row/@*'') as attr(insRow)

Como puede ver, convierto la fila en XML (Subquery select i, * for xml raw, esto convierte todas las columnas en una columna xml)

A CONTINUACIÓN, APLICARÉ UNA FUNCIÓN A cada atributo XML de esta columna, de modo que obtenga una fila por atributo.

En general, esto convierte las columnas en filas, sin conocer los nombres de las columnas y sin usar sql dinámico. Es lo suficientemente rápido para mi propósito.

(Editar: Acabo de ver la respuesta de Roman Pekar, que está haciendo lo mismo. Primero usé el disparador dinámico sql con cursores, que era 10 a 100 veces más lento que esta solución, pero tal vez fue causado por el cursor, no por el dynamic sql. De todos modos, esta solución es muy simple y universal, por lo que definitivamente es una opción).

Dejaré este comentario en este lugar, porque quiero hacer referencia a esta explicación en mi publicación sobre el activador de auditoría completo, que puede encontrar aquí: https://.com/a/43800286/4160788

Answer 3

Puede usar la función UNPIVOT para convertir las columnas en filas:

select id, entityId,
  indicatorname,
  indicatorvalue
from yourtable
unpivot
(
  indicatorvalue
  for indicatorname in (Indicator1, Indicator2, Indicator3)
) unpiv;

Tenga en cuenta que los tipos de datos de las columnas que está desvirtuando deben ser iguales, por lo que es posible que deba convertir los tipos de datos antes de aplicar el unpivot.

También puede usar CROSS APPLY con UNION ALL para convertir las columnas:

select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  select ''Indicator1'', Indicator1 union all
  select ''Indicator2'', Indicator2 union all
  select ''Indicator3'', Indicator3 union all
  select ''Indicator4'', Indicator4 
) c (indicatorname, indicatorvalue);

Dependiendo de su versión de SQL Server, incluso podría usar CROSS APPLY con la cláusula VALUES:

select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  values
  (''Indicator1'', Indicator1),
  (''Indicator2'', Indicator2),
  (''Indicator3'', Indicator3),
  (''Indicator4'', Indicator4)
) c (indicatorname, indicatorvalue);

Finalmente, si tiene 150 columnas para desvincular y no desea codificar la consulta completa, entonces puede generar la instrucción sql usando SQL dinámico:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @colsUnpivot 
  = stuff((select '',''+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = ''yourtable'' and
                 C.column_name like ''Indicator%''
           for xml path('''')), 1, 1, '''')

set @query 
  = ''select id, entityId,
        indicatorname,
        indicatorvalue
     from yourtable
     unpivot
     (
        indicatorvalue
        for indicatorname in (''+ @colsunpivot +'')
     ) u''

exec sp_executesql @query;

Answer 4

Solo para ayudar a los lectores nuevos, he creado un ejemplo para comprender mejor la respuesta de @ bluefeet sobre UNPIVOT.

 SELECT id
        ,entityId
        ,indicatorname
        ,indicatorvalue
  FROM (VALUES
        (1, 1, ''Value of Indicator 1 for entity 1'', ''Value of Indicator 2 for entity 1'', ''Value of Indicator 3 for entity 1''),
        (2, 1, ''Value of Indicator 1 for entity 2'', ''Value of Indicator 2 for entity 2'', ''Value of Indicator 3 for entity 2''),
        (3, 1, ''Value of Indicator 1 for entity 3'', ''Value of Indicator 2 for entity 3'', ''Value of Indicator 3 for entity 3''),
        (4, 2, ''Value of Indicator 1 for entity 4'', ''Value of Indicator 2 for entity 4'', ''Value of Indicator 3 for entity 4'')
       ) AS Category(ID, EntityId, Indicator1, Indicator2, Indicator3)
UNPIVOT
(
    indicatorvalue
    FOR indicatorname IN (Indicator1, Indicator2, Indicator3)
) UNPIV;

Answer 5

DECLARE @TableName nvarchar(50)
DECLARE column_to_row CURSOR FOR

--List of tables that we want to unpivot columns as row
SELECT DISTINCT t.name FROM sys.tables t
JOIN sys.schemas s ON t.schema_id=t.schema_id
WHERE t.name like ''%_CT%''
AND s.name=''cdc''

OPEN  column_to_row
FETCH NEXT FROM column_to_row INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @script nvarchar(max) = null
DECLARE @columns nvarchar(2000) = null

-- keep the table''s column list
select @columns = COALESCE(@columns + '','','''') + c.name from sys.tables  t
join sys.columns c on t.object_id = c.object_id
where t.name = @TableName

set @script = ''SELECT ''+@columns+'' FROM [cdc].[''+@TableName+''] (nolock)''
--print (@script)
exec (@script)

FETCH NEXT FROM column_to_row INTO @TableName
END
CLOSE column_to_row
DEALLOCATE column_to_row

Aquí hay otro método para columnas a filas, cuántas tablas y cuántas columnas tiene, no es importante. Simplemente establece los parámetros y obtén el resultado. Escribí esto, porque a veces necesito el resultado de una tabla A (que es el conjunto de resultados de columna), como campos de otra tabla B (que tiene que ser campos de fila). En ese caso, no sé cuántos campos he configurado para mi tabla B.