0

I was trying a PIVOT query and have a hard time understanding how we can assign multiple values to a variable.

My colleague pointed me to this query. This solved my problem but I am unable to understand this line

COALESCE(@Pivot_Column + ',', '') + QUOTENAME(Year)

Actually when a variable is assigned to a select with multiple values that last value is taken.

But if we observe all values are returned with above line.

Can someone help me understand?

DECLARE @Pivot_Column [nvarchar](max);  
DECLARE @Query [nvarchar](max);  
  
/*Select Pivot Column*/  
SELECT @Pivot_Column = COALESCE(@Pivot_Column + ',', '') + QUOTENAME(Year) 
FROM  
    (SELECT DISTINCT [Year] FROM Employee) Tab  
  
/* Create dynamic query */  
SELECT @Query = 'SELECT Name, ' + @Pivot_Column +
                'FROM   
                     (SELECT Name, [Year], Sales FROM Employee) Tab1  
                 PIVOT  
                     (SUM(Sales) FOR [Year] IN ('+@Pivot_Column+')) AS Tab2  
                 ORDER BY Tab2.Name'  
  
/* Execute query */  
EXEC sp_executesql @Query
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
suriya g
  • 9
  • 1
  • 1
    You can't assign multiple values to a scalar variable. `COALESCE` is just used to return the first not-NULL value from a list of values. Have you tried looking at [`STRING_AGG()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql) for your pivot operation? – AlwaysLearning Jul 17 '22 at 10:53
  • 1
    The method above is actually a [documented Anti-pattern](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-ver16#c-antipattern-use-of-recursive-variable-assignment) and should be avoided too. – Thom A Jul 17 '22 at 11:33
  • Thanks for the reply. Here is the link where my colleague has seen this example https://www.c-sharpcorner.com/UploadFile/f0b2ed/pivot-and-unpovit-in-sql-server/#:~:text=Pivot%20and%20Unpivot%20in%20SQL%20are%20two%20relational%20operators%20that,column%20level%20to%20row%20level. – suriya g Jul 17 '22 at 12:31
  • c-sharpcorner is not the most reliable tutorial. See also https://dba.stackexchange.com/a/132709/220697 for further links as to why this is wrong – Charlieface Jul 17 '22 at 15:16

0 Answers0