修复:消息102，级别15，状态1，第37行')'附近的语法不正确 [英] Fix: Msg 102, Level 15, State 1, Line 37 Incorrect syntax near ')'

问题描述

我正在使用MS SQL服务器.

I'm using MS SQL server.

我在查询中使用了数据透视.我已经完成的工作如下:

I have used pivot in my query. What I've done yet is given below:

--/*==========================================================
--
--
--
--===========================================================*/

--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应该在该列下.

Reason For Using Pivot in Procedure

Here concentration is dependent on Analyte. So what I want is that that depending on dynamic Analyte name its concentration should be under that column.

在这里，第一个@cols将包含将传递给 PIVOT 的所有analyte名称.最后的 exec(@stmt) 应该以 View

In here, first @cols will contain all the analyte names which will passed to the PIVOT. And the final exec(@stmt) should return the all data as a View

但是我遇到了错误: (10 row(s) affected) Msg 102, Level 15, State 1, Line 37 Incorrect syntax near ')'.

But I'm getting the error: (10 row(s) affected) Msg 102, Level 15, State 1, Line 37 Incorrect syntax near ')'.

而且，我是第一次使用数据透视.所以，如果有任何错误，也请告诉我.

And also, I'm using pivot first time. So Please let me know if any error in it also.

推荐答案

以逗号分隔的列列表的构建方式需要更改.将其更改为:

The way you build up the comma-separated list of columns needs to be change. Change that to:

DECLARE @cols NVARCHAR(MAX) = N''

SELECT @cols = @cols + N', ' + Table3.vAnalyteName
FROM Table3,BaSequenceScheduleHdr,BAAnalyteSampleHdr
WHERE Table3.nTable2No = BaSequenceScheduleHdr.nTable2No
AND Table3.cStatusIndi <> 'D'

set @cols = stuff(@cols, 1, 2, N'') 

这篇关于修复:消息102，级别15，状态1，第37行')'附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！