Como obter dados PIVOT de consulta MSSQL e buscar para visualização PHP

Question

Eu tenho um banco de dados MSSQL 2005 com conteúdo de consulta como este:

nopas	código	nome	ordem
00115301	D031	ADÃO	1
00130015	D031	ADÃO	2
00149998	D026	JAMES	1
00149970	D028	FIKI	1
00142641	D028	FIKI	2
00127700	D028	FIKI	3

Por motivo de relatório, preciso gerar uma tabela como:

ADÃO	JAMES	FIKI	... (novo registro baseado em conteúdo dinâmico)
00115301	00149998	00149970	...
00130015	-	00142641	...
-	-	00127700	...

Alguém pode me ajudar??

Answer 1

Você pode fazer um pivô com CASEdeclarações. É basicamente a forma manual de dinamização e se você estava usando uma versão do SQL Server antes de 2005, você precisaria seguir esse caminho. O SQL Server 2005 adicionou a função PIVOT .

Como você precisa que ele seja dinâmico, você precisará usar SQL dinâmico . É muito trabalho de perna, mas definitivamente factível.

Vou criar o conteúdo da sua tabela em uma tabela temporária global (tem que ser global para funcionar em 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);

Em seguida, vou SELECTapenas os nomes exclusivos em outra tabela temporária e associá-los a um IDENTITYcom o qual posso iterar em um loop for.

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

Agora vem a parte difícil. O PRINT @dynSQLgera o que estou tentando alcançar dinamicamente (abaixo). Precisamos SELECTdo [order], [name]e [nopas]para o resultado final. Estamos girando na [name]segunda parte da instrução e agregando por MAX([nopas]); mas como temos um [order]agrupamento no conteúdo da tabela original, ele recuperará apenas o com MAX([nopas])base no MAXvalor de uma única linha. Portanto, o resultado é que ele remove a agregação. Se você não tem uma [order]coluna em seu conteúdo original, seria preciso usar a função ROW_NUMBER () * PARTITIONED BYo [name]para gerar esse [order].

EDIT: descobri que a ROW_NUMBER()função está disponível apenas no SQL Server 2012 e mais recente.

-- 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, se você quiser remover a [order]coluna, do resultado. Você pode usar SELECT * INTO ##newGlobalTableno SQL dinâmico e, em seguida, ALTERa nova tabela e DROPa coluna.