Fix: Msg 102, Level 15, State 1, Line 37 Incorrect

Question

I'm using MS SQL server.

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

SQL Procedure

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

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

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.

How it should be work

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

---

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.

Answer 1

This part is not making sense

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

Answer 2

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'')