Incorrect Syntax near SqlQuery

Hello Ramco,

You're facing a syntax error in your SQL code for multiple reasons, including:

1. Incorrect use of STRING_AGG in variable assignment
2. Using an alias (cols) that is not assigned to a variable
3. Incorrect dynamic SQL execution

Here’s the corrected version of your SQL query:

DECLARE @ColumnNames NVARCHAR(MAX);
DECLARE @SQLQuery NVARCHAR(MAX);

-- Fetch column names dynamically
SELECT @ColumnNames = STRING_AGG(QUOTENAME(T2."U_A_M"), ', ')
FROM (SELECT DISTINCT T2."U_A_M" FROM Oitm T2) AS cols;

-- Construct the dynamic SQL query
SET @SQLQuery =  
'SELECT ItemCode, ' + @ColumnNames + ' 
FROM (
    SELECT T1."ItemCode", T1."LineTotal", T2."U_A_M"
    FROM Opch T0 
    INNER JOIN Pch1 T1 ON T0."Docentry" = T1."DocEntry"
    INNER JOIN Oitm T2 ON T1."ItemCode" = T2."ItemCode"
    WHERE T2."U_A_M" <> ''NA''
) x
PIVOT (
    SUM(LineTotal) 
    FOR U_A_M IN (' + @ColumnNames + ')
) p';

-- Execute the dynamic SQL
EXEC sp_executesql @SQLQuery;

Good Luck!

The error you're encountering, "Incorrect Syntax near SqlQuery," is often caused by incorrect SQL syntax within your query. In your provided code snippet, I've identified a couple of issues:

1. The declaration of `ColumnNames` and `SQLQuery` seems correct, but there is a missing `END` statement since the `DECLARE` block should end accordingly in T-SQL.

2. The usage of double quotes around `"U_A_M"` in the `STRING_AGG` function might be causing issues. In SQL, double quotes are typically used around identifiers, while single quotes are used for string literals.

3. In your dynamic SQL construction, you are referencing the `cols` variable directly within the string concatenation `'+ cols +'`, which might not expand as expected in dynamic SQL.

To address these issues:

- Ensure that your `DECLARE` block is correctly ended with `END`.

- Replace double quotes around `"U_A_M"` with single quotes, like `'U_A_M'`.

- Make sure the `cols` variable is properly concatenated within the dynamic SQL statement. You might need to adjust how you concatenate this variable to ensure it expands correctly within the query string.

Here's a revised code snippet based on the identified issues:

DECLARE @ColumnNames nvarchar(100);
DECLARE @SQLQuery nvarchar(100);

SELECT @ColumnNames = STRING_AGG('U_A_M', ', ')  
FROM (SELECT DISTINCT T2.U_A_M
            FROM Oitm T2) As cols;

SET @SQLQuery =  'SELECT itemcode, ' + @ColumnNames + ' from
            (
                SELECT T1.ItemCode, T1.LineTotal, T2.U_A_M
    FROM Opch T0 inner join Pch1 T1 on T0.Docentry = T1.DocEntry
	inner join Oitm T2 on T1.ItemCode = T2.ItemCode
	where T2.U_A_M <> ''NA''
           ) x
            pivot
            (
                 sum(linetotal)
                for U_A_M in (' + @ColumnNames + ')
            ) p '

EXEC sp_executesql @SQLQuery

In this revised version:

- Single quotes are used consistently for string literals.

- The `cols` variable is replaced with `@ColumnNames` within the dynamic SQL statement.

- `sp_executesql` is used to execute the dynamically generated SQL query.

These adjustments should help address the syntax error you were facing near `SQLQuery`. Feel free to give this revised code a try and let me know if you encounter any further issues or have additional questions.