Как сделать PIVOT-данные из запроса MSSQL и извлечь в представление PHP

Question

У меня есть база данных MSSQL 2005 с таким содержанием запроса:

нопа	код	имя	порядок
00115301	D031	АДАМ	1
00130015	D031	АДАМ	2
00149998	D026	ДЖЕЙМС	1
00149970	D028	ФИКИ	1
00142641	D028	ФИКИ	2
00127700	D028	ФИКИ	3

Для отчета мне нужно вывести такую ​​таблицу, как:

АДАМ	ДЖЕЙМС	ФИКИ	... (новая запись на основе динамического содержимого)
00115301	00149998	00149970	...
00130015	-	00142641	...
-	-	00127700	...

Может кто-нибудь мне помочь??

Answer 1

Вы можете сделать поворот с помощью CASEутверждений. По сути, это ручной способ поворота, и если вы использовали версию SQL Server до 2005 года, вам нужно было бы пойти по этому пути. В SQL Server 2005 добавлена функция PIVOT .

Поскольку вам нужно, чтобы он был динамическим, вам нужно будет использовать динамический SQL . Это большая работа ног, но определенно выполнимая.

Я собираюсь создать содержимое вашей таблицы в глобальной временной таблице (она должна быть глобальной для работы в динамическом SQL).

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

Затем я собираюсь добавить SELECTуникальные имена в другую временную таблицу и связать их с элементом, IDENTITYс которым я могу выполнять итерацию в цикле for.

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

Теперь самое сложное. PRINT @dynSQLПорождает то , что я пытаюсь добиться динамического (ниже). Нам нужно SELECTв [order], [name]и [nopas]для конечного результата. Мы переходим ко [name]второй части инструкции и агрегируем по MAX([nopas]); но поскольку мы содержали [order]группировку в исходном содержимом таблицы, он будет извлекать только на MAX([nopas])основе MAXзначения одной строки. Итак, в результате удаляется агрегация. Если у вас не было [order]столбца в исходном содержимом, нам нужно будет использовать функцию ROW_NUMBER () * PARTITIONED BYthe [name]для его создания [order].

EDIT: я обнаружил, что ROW_NUMBER()функция доступна только в SQL Server 2012 и новее.

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

---

О, если вы хотите убрать [order]столбец из результата. Вы можете использовать SELECT * INTO ##newGlobalTableдинамический SQL, а затем ALTERновую таблицу и DROPстолбец.