para - pivot sin funcion de agregado sql server
Servidor SQL: Transponer filas a columnas (5)
¿Además de escribir el cursor leyendo cada fila y poblarla en columnas, cualquier otra alternativa si necesito transponer cada fila en columnas?
TimeSeconds TagID Value
1378700244 A1 3.75
1378700245 A1 30
1378700304 A1 1.2
1378700305 A2 56
1378700344 A2 11
1378700345 A3 0.53
1378700364 A1 4
1378700365 A1 14.5
1378700384 A1 144
1378700384 A4 10
La cantidad de columnas no es fija.
Salida: Acabo de asignar n / a como marcador de posición para ningún dato en esa intersección.
TimeSec A1 A2 A3 A4
1378700244 3.75 n/a n/a n/a
1378700245 30 n/a n/a n/a
1378700304 1.2 n/a n/a n/a
1378700305 n/a 56 n/a n/a
1378700344 n/a 11 n/a n/a
1378700345 n/a n/a 0.53 n/a
1378700364 n/a n/a n/a 4
1378700365 14.5 n/a n/a n/a
1378700384 144 n/a n/a 10
Espero que puedas compartir conmigo algunos consejos. Gracias.
Basado en la solution de bluefeet aquí hay un procedimiento almacenado que usa sql dinámico para generar la tabla transpuesta. Requiere que todos los campos sean numéricos a excepción de la columna transpuesta (la columna que será el encabezado en la tabla resultante):
/****** Object: StoredProcedure [dbo].[SQLTranspose] Script Date: 11/10/2015 7:08:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Paco Zarate
-- Create date: 2015-11-10
-- Description: SQLTranspose dynamically changes a table to show rows as headers. It needs that all the values are numeric except for the field using for transposing.
-- Parameters: @TableName - Table to transpose
-- @FieldNameTranspose - Column that will be the new headers
-- Usage: exec SQLTranspose <table>, <FieldToTranspose>
-- table and FIeldToTranspose should be written using single quotes
-- =============================================
ALTER PROCEDURE [dbo].[SQLTranspose]
-- Add the parameters for the stored procedure here
@TableName NVarchar(MAX) = '''',
@FieldNameTranspose NVarchar(MAX) = ''''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@queryPivot AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX),
@columnToPivot as NVARCHAR(MAX),
@tableToPivot as NVARCHAR(MAX),
@colsResult as xml
select @tableToPivot = @TableName;
select @columnToPivot = @FieldNameTranspose
select @colsUnpivot = stuff((select '',''+quotename(C.name)
from sys.columns as C
where C.object_id = object_id(@tableToPivot) and
C.name <> @columnToPivot
for xml path('''')), 1, 1, '''')
set @queryPivot = ''SELECT @colsResult = (SELECT '''',''''
+ quotename(''+@columnToPivot+'')
from ''+@tableToPivot+'' t
where ''+@columnToPivot+'' <> ''''''''
FOR XML PATH(''''''''), TYPE)''
exec sp_executesql @queryPivot, N''@colsResult xml out'', @colsResult out
select @colsPivot = STUFF(@colsResult.value(''.'', ''NVARCHAR(MAX)''),1,1,'''')
set @query
= ''select name, rowid, ''+@colsPivot+''
from
(
select ''+@columnToPivot+'' , name, value, ROW_NUMBER() over (partition by ''+@columnToPivot+'' order by ''+@columnToPivot+'') as rowid
from ''+@tableToPivot+''
unpivot
(
value for name in (''+@colsUnpivot+'')
) unpiv
) src
pivot
(
sum(value)
for ''+@columnToPivot+'' in (''+@colsPivot+'')
) piv
order by rowid''
exec(@query)
END
Otra opción que puede ser adecuada en esta situación es el uso de XML
La opción XML para transponer filas en columnas es básicamente una versión óptima de PIVOT en que aborda la limitación dinámica de la columna.
La versión XML de la secuencia de comandos aborda esta limitación mediante el uso de una combinación de ruta XML, T-SQL dinámico y algunas funciones incorporadas (es decir, STUFF, QUOTENAME).
Expansión vertical
De forma similar a PIVOT y Cursor, las políticas recién agregadas se pueden recuperar en la versión XML del script sin alterar el script original.
Expansión horizontal
A diferencia del PIVOT, los documentos recién agregados pueden mostrarse sin alterar el guión.
Desglose de rendimiento
En términos de IO, las estadísticas de la versión XML del script son casi similares a las de PIVOT: la única diferencia es que el XML tiene un segundo escaneo de la tabla dtTranspose, pero esta vez de un caché de datos de lectura lógica.
Puede encontrar más información sobre estas soluciones (incluidos algunos ejemplos reales de T-SQL) en este artículo: https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/
SQL Server tiene un comando PIVOT que podría ser lo que estás buscando.
select * from Tag
pivot (MAX(Value) for TagID in ([A1],[A2],[A3],[A4])) as TagTime;
Si las columnas no son constantes, deberá combinar esto con algún SQL dinámico.
DECLARE @columns AS VARCHAR(MAX);
DECLARE @sql AS VARCHAR(MAX);
select @columns = substring((Select DISTINCT '','' + QUOTENAME(TagID) FROM Tag FOR XML PATH ('''')),2, 1000);
SELECT @sql =
''SELECT *
FROM TAG
PIVOT
(
MAX(Value)
FOR TagID IN( '' + @columns + '' )) as TagTime;'';
execute(@sql);
Tenía un requisito ligeramente diferente, por el cual tenía que transponer selectivamente columnas en filas.
La tabla tenía columnas:
create table tbl (ID, PreviousX, PreviousY, CurrentX, CurrentY)
Necesitaba columnas para Previous y Current , y las filas para X e Y Un producto cartesiano generado en una tabla estática funciona bien, por ejemplo:
select
ID,
max(case when metric=''X'' then PreviousX
case when metric=''Y'' then PreviousY end) as Previous,
max(case when metric=''X'' then CurrentX
case when metric=''Y'' then CurrentY end) as Current
from tbl inner join
/* Cartesian product - transpose by repeating row and
picking appropriate metric column for period */
( VALUES (1, ''X''), (2, ''Y'')) AS x (sort, metric) ON 1=1
group by ID
order by ID, sort
Una forma de hacerlo si los valores de tagID se conocen por adelantado es usar la agregación condicional
SELECT TimeSeconds,
COALESCE(MAX(CASE WHEN TagID = ''A1'' THEN Value END), ''n/a'') A1,
COALESCE(MAX(CASE WHEN TagID = ''A2'' THEN Value END), ''n/a'') A2,
COALESCE(MAX(CASE WHEN TagID = ''A3'' THEN Value END), ''n/a'') A3,
COALESCE(MAX(CASE WHEN TagID = ''A4'' THEN Value END), ''n/a'') A4
FROM table1
GROUP BY TimeSeconds
o si está bien con valores NULL lugar de ''n/a''
SELECT TimeSeconds,
MAX(CASE WHEN TagID = ''A1'' THEN Value END) A1,
MAX(CASE WHEN TagID = ''A2'' THEN Value END) A2,
MAX(CASE WHEN TagID = ''A3'' THEN Value END) A3,
MAX(CASE WHEN TagID = ''A4'' THEN Value END) A4
FROM table1
GROUP BY TimeSeconds
o con PIVOT
SELECT TimeSeconds, A1, A2, A3, A4
FROM
(
SELECT TimeSeconds, TagID, Value
FROM table1
) s
PIVOT
(
MAX(Value) FOR TagID IN (A1, A2, A3, A4)
) p
Salida (con NULL s):
TimeSeconds A1 A2 A3 A4 ----------- ------- ------ ----- ----- 1378700244 3.75 NULL NULL NULL 1378700245 30.00 NULL NULL NULL 1378700304 1.20 NULL NULL NULL 1378700305 NULL 56.00 NULL NULL 1378700344 NULL 11.00 NULL NULL 1378700345 NULL NULL 0.53 NULL 1378700364 4.00 NULL NULL NULL 1378700365 14.50 NULL NULL NULL 1378700384 144.00 NULL NULL 10.00
Si tiene que calcular los valores de TagID forma dinámica, utilice SQL dinámico
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)
SET @cols = STUFF((SELECT DISTINCT '','' + QUOTENAME(TagID)
FROM Table1
ORDER BY 1
FOR XML PATH(''''), TYPE
).value(''.'', ''NVARCHAR(MAX)''),1,1,'''')
SET @sql = ''SELECT TimeSeconds, '' + @cols + ''
FROM
(
SELECT TimeSeconds, TagID, Value
FROM table1
) s
PIVOT
(
MAX(Value) FOR TagID IN ('' + @cols + '')
) p''
EXECUTE(@sql)