the pivot () function in SQL Server implements dynamic row-to-column

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