The working of pivot depends upon the columns we specify in the select list. The pivot has one column on which aggregate function is applied (like sum(),count(), etc), the second column from which the data is rotated and pivot implicitly applies GROUP BY clause on rest of columns.

For E.g :

Select objid, attr1, attr2, Col_data1, Col_data2

from

(

Select num, objid, attr1, attr2, attr3 from Table_Name

) as Raw_data

pivot

(

sum(num) for attr3 in (Col_data1, Col_data2)

) as pvt

In this case, the aggregate function sum() is applied on column "num". Col_data1 and Col_data2 are the row values which will form new columns in the output table. Now the pivot implicitly applies GROUP BY to all those columns in Select list which do not take part in PIVOT, in this case objid, attr1, attr2. So we need to be careful in selecting the columns. So use a derived query to select the required columns instead of using "Select * from table_name". Since it applies GROUP BY in this fashion we might end up applying GROUP BY to all those columns which we dont want resulting in wrong results as mentioned in this post .