In general, the pivot () function can only be written to die in, because pivot () in does not support subqueries, so this can only be solved with stored procedures;
CREATE PROCEDURE Insurancepivot
@Foldercode VARCHAR (50),
@isY VARCHAR (4)
As
BEGIN DECLARE @ColumnNames VARCHAR (3000)
SET @ColumnNames = ' '
SELECT @ColumnNames = @ColumnNames + ' [' + Testno + '], '
From (SELECT Testno from Ordtask
Left joins TESTS on TESTS. Testcode = Ordtask. Testcode
WHERE Ordtask. Folderno in (SELECT folderno from FOLDERS WHERE foldercode = @Foldercode)) t
SET @ColumnNames = Left (@ColumnNames, LEN (@ColumnNames)-1)
DECLARE @selectSQL NVARCHAR (3000)
SET @selectSQL = ' Select p.* from (select Foldercode as "Accept single number", Ordcode as "sample number", Orders. Originalno as "original number", Orders.name as "name", Pa. Sex as "gender", Pa. Ages as "age", Pa. Address as "present address", folders. Daterecv as "collection Date", Final,name. Testcode, orders. Samplingdate, Testno
From orders left join folders on orders.folderno = Folders.folderno
Left joins PATIENTS PA on Pa. PID = orders. Pid
Left join (select Ordno, results. Final,tests.testno, Tests.testcode
From analytes
Left joins Ordtask on Ordtask. Testcode = analytes. Testcode and Ordtask. METHOD = analytes. METHOD
Left joins results on results. analyte = analytes. Analyte and results. Ordtaskno = Ordtask. Ordtaskno
Left JOIN tests on tests.testcode = analytes. Testcode
where Analytes.displayflag = ' [email protected]+ '] name on name.ordno = orders.ordno where foldercode = ' [Email pr Otected]+ ") A
Pivot (max (FINAL) for Testno in ({0})) P '
SET @selectSQL = REPLACE (@selectSQL, ' {0} ', @ColumnNames)
EXEC sp_executesql @selectSQL
END
GO
the pivot () function in SQL Server implements dynamic row-to-column