Cómo PIVOTar datos de una consulta MSSQL y obtenerlos en la vista PHP

Dec 04 2020

Tengo una base de datos MSSQL 2005 con contenido de consulta como este:

nopas código nombre orden
00115301 D031 ADÁN 1
00130015 D031 ADÁN 2
00149998 D026 JAMES 1
00149970 D028 FIKI 1
00142641 D028 FIKI 2
00127700 D028 FIKI 3

Por el motivo del informe, necesito generar una tabla como:

ADÁN JAMES FIKI ... (nuevo registro basado en contenido dinámico)
00115301 00149998 00149970 ...
00130015 - 00142641 ...
- - 00127700 ...

¿¿Alguien puede ayudarme??

Respuestas

Jason Dec 04 2020 at 16:46

Puede hacer un pivote con CASEdeclaraciones. Es básicamente la forma manual de pivotar y si estuviera usando una versión de SQL Server antes de 2005, necesitaría seguir ese camino. SQL Server 2005 agregó la función PIVOT .

Debido a que necesita que sea dinámico, necesitará utilizar SQL dinámico . Es mucho trabajo de campo, pero definitivamente es factible.

Voy a crear el contenido de su tabla en una tabla temporal global (tiene que ser global para trabajar en SQL dinámico).

-- CREATE GLOBAL TEMPORARY TABLE
CREATE TABLE ##tablename (
  [nopas] VARCHAR(8),
  [code] VARCHAR(4),
  [name] VARCHAR(64),
  [order] SMALLINT
);

INSERT INTO ##tablename
  ([nopas], [code], [name], [order])
VALUES
 ('00115301','D031','ADAM',  1)
,('00130015','D031','ADAM',  2)
,('00149998','D026','JAMES', 1)
,('00149970','D028','FIKI',  1)
,('00142641','D028','FIKI',  2)
,('00127700','D028','FIKI',  3);

A continuación, voy a SELECTincluir los nombres únicos en otra tabla temporal y los asociaré con una IDENTITYcon la que puedo iterar en un bucle for.

SELECT IDENTITY(INT, 1,1) as [id], [name] 
INTO #temp
FROM ##tablename
GROUP BY [name];

Ahora viene la parte difícil. El PRINT @dynSQLgenera lo que estoy tratando de lograr de forma dinámica (abajo). Necesitamos SELECTel [order], [name]y [nopas]para el resultado final. Estamos girando sobre el [name]en la segunda parte de la declaración y agregando por MAX([nopas]); pero debido a que tenemos una [order]agrupación en el contenido de su tabla original, solo recuperará el MAX([nopas])basado en el MAXvalor de una sola fila. Entonces, el resultado es que elimina la agregación. Si no tenía una [order]columna en su contenido original, necesitaríamos usar la función ROW_NUMBER () * PARTITIONED BYthe [name]para generarla [order].

EDITAR: descubrí que la ROW_NUMBER()función solo está disponible en SQL Server 2012 y versiones posteriores.

-- PRINT @dynSQL result
SELECT * FROM
(SELECT [order], [name], [nopas]
  FROM ##tablename) AS sourceTable
PIVOT(
MAX([nopas])
FOR [name] IN ([ADAM],[FIKI],[JAMES])
) AS pivotTable;
-- The leg work to create the dynamic SQL.
DECLARE @count INT = 1;
DECLARE @max INT = (SELECT MAX(id) FROM #temp);
DECLARE @dynSQL VARCHAR(2048) = 'SELECT * FROM
(SELECT [order], [name], [nopas]
  FROM ##tablename) AS sourceTable
PIVOT(
MAX([nopas])
FOR [name] IN (';

SET @count = 1;
WHILE(@count <= @max)
BEGIN
  SET @dynSQL += '[' + (SELECT [name] FROM #temp WHERE id = @count) + '],';
  SET @count += 1;
END

SET @dynSQL = LEFT(@dynSQL, DATALENGTH(@dynSQL) -1) + ')
) AS pivotTable;';

PRINT @dynSQL;
EXEC (@dynSQL);

DROP TABLE ##tablename;
DROP TABLE #temp;

Oh, si quieres eliminar la [order]columna, del resultado. Puede usar SELECT * INTO ##newGlobalTableen el SQL dinámico y luego ALTERla nueva tabla y DROPla columna.