SELECT **@COLS** = STUFF((SELECT DISTINCT ',' + QUOTENAME(BR.EMPLOYEENAME) FROM **#TMP_RESULTS** BR  FOR XML PATH('')),1,1,''); 

SET @QUERY = 'INSERT INTO #RESULTS SELECT DISTINCT P.EMP_ID,'+**@COLS**+' FROM     
      (                  
      SELECT EMP_ID,EMPLOYEENAME,OFFICE_ID,VALUE FROM **#TMP_RESULTS** BR) AS X    
      PIVOT     
      (    
       MAX( X.VALUE)    
       FOR X.EMPLOYEENAME IN (' + **@COLS** + ')    
      ) AS P '; 

2 Answers

0
Kusuma S V On Best Solutions

Here EmployeeName is the Column whose values become the column headers when Pivoted. @COLS is using Distinct keyword on EmployeeName column because of which the order of the EmployeeNames selected will change. When using Pivot table in SQL, it expects @COLS (ie., Column headers/EmployeeName) to be in the same order as in the source table that is TMP_RESULTS. But @COLS is having different order of EmployeeNames. Due to this, we get undesired results in #RESULTS table. That is, Column Headers are interchanged for the values. To solve this, we can use Distinct and group by on ID, or we can remove Distinct keyword and add distinct column-headers coming from xml path into a dummy table, and since same order will be used in TMP_RESULTS table also, pivoting will not interchange the results.

0
Kusuma S V On

@COLS TAKES VALUES FROM TEMPPARAMETERS AND NOT TMP_RESUTLS.

TMP_RESULTS has mutiple Values for each of the EmployeeNames. The EmployeeNames are in proper order, as sent in XML. Both TEMPPARAMETERS and TMP_RESUTLS have the same order of EmployeeNames. TEMPPARAMETERS has distinct EmployeeNames initially taken from XML. Using 'Distinct' again on it, will change the order of EmployeeNames which is being added to @COLS. Now @COLS and TMP_RESUTLS's EmployeeNames orders are different. Hence using @COLS as headers and MAX(Values) of TMP_RESUTLS, will interchange the results in the columns. Meaning Distinct is changing the order of columns, which has a bad impact on Pivot table results.