T-SQL LANGUAGE ENHANCEMENTS SELECT * FROM SALES PIVOT

T-SQL LANGUAGE ENHANCEMENTS SELECT * FROM SALES PIVOT (SUM (Amount) FOR [Quarter] IN (Q2)) AS P GO Year Q2 – 2001 190 2002 250 Note that the output produced by the PIVOT clause acts as though SELECT has a GROUP BY [Year] clause. A pivot, in effect, applies a GROUP BY to the SELECT that includes all the columns that are not either the aggregate or the pivot column. This can lead to undesired results, as shown in the SQL batch that follows. It uses essentially the same SALES table as the previous example, except that it has an additional column named Other. CREATE TABLE SALES2 ( [Year] INT, Quarter CHAR(2), Amount FLOAT, Other INT ) INSERT INTO SALES2 VALUES (2001, Q2 , 70, 1) INSERT INTO SALES2 VALUES (2001, Q3 , 55, 1) INSERT INTO SALES2 VALUES (2001, Q3 , 110, 2) INSERT INTO SALES2 VALUES (2001, Q4 , 90, 1) INSERT INTO SALES2 VALUES (2002, Q1 , 200, 1) INSERT INTO SALES2 VALUES (2002, Q2 , 150, 1) INSERT INTO SALES2 VALUES (2002, Q2 , 40, 1) INSERT INTO SALES2 VALUES (2002, Q2 , 60, 1) INSERT INTO SALES2 VALUES (2002, Q3 , 120, 1) INSERT INTO SALES2 VALUES (2002, Q3 , 110, 1) INSERT INTO SALES2 VALUES (2002, Q4 , 180, 1) SELECT * FROM Sales2 PIVOT (SUM (Amount) FOR Quarter IN (Q3)) AS P GO

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

Bookmark the permalink.

Comments are closed.