我正在使用MS SQL Server。
我在查询中使用了透视 。 我已经完成的工作如下:
SQL过程
--/*==========================================================
--
--
--
--===========================================================*/
--CREATE VIEW View_BASequenceExportDtl
--AS
--ALTER PROCEDURE Proc_BASequenceExportDtl
--AS
DECLARE @cols NVARCHAR(MAX)
DECLARE @stmt NVARCHAR(MAX)
SET @cols = ISNULL(@cols + ', ', '') (
SELECT Table3.vAnalyteName
FROM Table3,BaSequenceScheduleHdr,BAAnalyteSampleHdr
WHERE Table3.nTable2No = BaSequenceScheduleHdr.nTable2No
AND Table3.cStatusIndi <> 'D'
)
SET @stmt = 'SELECT
Table1.nBaSequenceScheduleHdrNo,
Table1.vSampleId,
Table1.vSampleName,
Table1.iSeqNo,
Table1.vSampleType,
Table2.vInjectionVolume,
Table3.vAnalyteName,
Table4.nConcentration,
Table1.cStatusIndi
FROM Table1
INNER JOIN BaSequenceScheduleHdr
ON(BaSequenceScheduleHdr.nBaSequenceScheduleHdrNo = Table1.nBaSequenceScheduleHdrNo
AND BaSequenceScheduleHdr.cStatusIndi <> ''D'')
INNER JOIN Table2
ON(Table2.nTable2No = BaSequenceScheduleHdr.nTable2No
AND Table2.cStatusIndi <> ''D'')
INNER JOIN Table3
ON(Table3.nTable2No = BaSequenceScheduleHdr.nTable2No
AND Table3.cStatusIndi <> ''D'')
LEFT JOIN BAAnalyteSampleHdr
ON(BAAnalyteSampleHdr.nTable2No = BaSequenceScheduleHdr.nTable2No
AND BAAnalyteSampleHdr.nTable3No = Table3.nTable3No
AND BAAnalyteSampleHdr.cStatusIndi <> ''D'')
LEFT JOIN Table4
ON(Table4.nBAAnalyteSampleHdrNo = BAAnalyteSampleHdr.nBAAnalyteSampleHdrNo
AND Table4.vSampleID = Table1.vSampleId
AND Table4.cStatusIndi <> ''D'')
PIVOT (
SUM(Table4.nConcentration) FOR Table3.vAnalyteName IN ( ' + @cols + ' )
) AS PVT'
exec(@stmt)
在过程中使用数据透视的原因
这里的concentration取决于被Analyte 。 所以我想要的是,根据动态Analyte名称,其concentration应在该列下。
应该如何工作
在这里,第一个@cols将包含所有将传递给PIVOT的analyte名称。 最后的exec(@stmt)应该以View返回所有数据
但是我遇到了错误:( (10 row(s) affected) Msg 102, Level 15, State 1, Line 37 Incorrect syntax near ')'.
而且,我是第一次使用数据透视 。 因此,如果有任何错误,也请告诉我。