CODEWITHSUNDEEP
sql-servert-sqlpivot
John Richard
John Richard

Reputation: 47

T sql CONCAT by comma dynamic query

I have dynamic sql query as below.

DECLARE @cols   AS NVARCHAR(MAX) = '[0],[3],[11]',
        @query    AS NVARCHAR(MAX)

SET @query = N'SELECT * FROM 
                (
                   SELECT 
                   year(createdDate) as [year],month(createdDate) as [month],cp.product_Id as product_ID,cp.salesprice as Amount 
                   FROM customer_products cp               
               )s 
               PIVOT 
               (
               SUM(Amount) 
               FOR product_Id IN ( '+ @cols +' )) 
AS pvt;' 

EXECUTE(@query)

Question:

Above query works however below query is not working because of

 SELECT @cols = CONCAT(@cols, '[', cast(product_ID as varchar),']') FROM Product

code block.Error displays Incorrect syntax near

DECLARE @cols   AS NVARCHAR(MAX)  = '',
        @query    AS NVARCHAR(MAX)

SELECT @cols = CONCAT(@cols, '[', cast(product_ID as varchar),'],') FROM Product

    SET @query = N'SELECT * FROM 
                    (
                       SELECT 
                       year(createdDate) as [year],month(createdDate) as [month],cp.product_Id as product_ID,cp.salesprice as Amount 
                       FROM customer_products cp               
                   )s 
                   PIVOT 
                   (
                   SUM(Amount) 
                   FOR product_Id IN ( '+ @cols +' )) 
    AS pvt;' 

    EXECUTE(@query)

Where i miss exactly what is missing in above query while selecting productID from Product ?

Upvotes: 1

Views: 319

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

You need to remove last , from @cols, add

SET @cols = LEFT(@cols, LEN(@cols) - 1)

You can consider using XML instead CONCAT like:

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(CAST(product_ID as VARCHAR(10)))
                      FROM Products
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                    , 1, 1, '');

It is a good practice to define length for cast(product_ID as varchar(10))

Upvotes: 2

John Bell
John Bell

Reputation: 2350

You need to ensure the last comma is removed from the select list, or you're passing an empty value to PIVOT.

Use this:

DECLARE @cols   AS NVARCHAR(MAX)  = '',
        @query    AS NVARCHAR(MAX)

SELECT @cols = COALESCE(@cols+', ','') + '[' + cast(product_ID as varchar) + ']' FROM product

    SET @query = N'SELECT * FROM 
                    (
                       SELECT 
                       year(createdDate) as [year],month(createdDate) as [month],cp.product_Id as product_ID,cp.salesprice as Amount 
                       FROM customer_products cp               
                   )s 
                   PIVOT 
                   (
                   SUM(Amount) 
                   FOR product_Id IN ( '+ @cols +' )) 
    AS pvt;' 

    EXECUTE(@query)

Upvotes: 1

Related Questions