Jak przestawiać dane z zapytania MSSQL i pobierać do widoku PHP

Dec 04 2020

Mam bazę danych MSSQL 2005 z następującą zawartością zapytania:

nopas kod Nazwa zamówienie
00115301 D031 ADAM 1
00130015 D031 ADAM 2
00149998 D026 JAMES 1
00149970 D028 FIKI 1
00142641 D028 FIKI 2
00127700 D028 FIKI 3

Z powodu raportu potrzebuję wypisać tabelę taką jak:

ADAM JAMES FIKI ... (nowy rekord oparty na zawartości dynamicznej)
00115301 00149998 00149970 ...
00130015 - 00142641 ...
- - 00127700 ...

Czy ktoś może mi pomóc??

Odpowiedzi

Jason Dec 04 2020 at 16:46

Możesz zrobić zwrot z CASEinstrukcjami. Zasadniczo jest to ręczny sposób przestawiania i jeśli używasz wersji SQL Server przed 2005 rokiem, musisz iść tą drogą. SQL Server 2005 dodał funkcję PIVOT .

Ponieważ chcesz, aby był dynamiczny, będziesz musiał używać dynamicznego SQL . To dużo pracy nóg, ale zdecydowanie wykonalne.

Mam zamiar utworzyć zawartość tabeli w globalnej tabeli tymczasowej (musi być globalna, aby działać w dynamicznym 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);

Następnie zamierzam SELECTumieścić tylko unikalne nazwy w innej tabeli tymczasowej i skojarzyć je z tabelą, za IDENTITYpomocą której mogę iterować w pętli for.

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

Teraz najtrudniejsza część. PRINT @dynSQLGeneruje co próbuję osiągnąć dynamicznie (poniżej). Musimy SELECTpo [order], [name]i [nopas]na wynik końcowy. Przechodzimy [name]do drugiej części oświadczenia i agregujemy według MAX([nopas]); ale ponieważ [order]w oryginalnej zawartości tabeli umieściliśmy grupę, pobierze ona dane tylko na MAX([nopas])podstawie MAXwartości pojedynczego wiersza. W rezultacie usuwa agregację. Jeśli nie masz [order]kolumny w swojej oryginalnej treści, musielibyśmy użyć funkcji ROW_NUMBER () * PARTITIONED BYthe, [name]aby ją wygenerować [order].

EDYCJA: Odkryłem, że ROW_NUMBER()funkcja jest dostępna tylko w SQL Server 2012 i nowszych.

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

Och, jeśli chcesz usunąć [order]kolumnę z wyniku. Możesz użyć SELECT * INTO ##newGlobalTabledynamicznego SQL, a następnie ALTERnowej tabeli i DROPkolumny.