T-SQL ENHANCEMENTS Year Other Q3 – – 2001 1

T-SQL ENHANCEMENTS Year Other Q3 – – 2001 1 55 2002 1 115 2001 2 110 Note that the year 2001 appears twice, once for each value of Other. The SELECT that precedes the PIVOTkeyword cannot specify which columns to use in the PIVOT clause. However, a subquery can be used to eliminate the columns not desired in the pivot, as shown in the SQL batch that follows. SELECT * FROM (Select Amount, Quarter, Year from Sales2 ) AS A PIVOT (SUM (Amount) FOR Quarter IN (Q3)) AS P GO Year Q3 – 2001 165 2002 230 A column named in the FOR part of the PIVOT clause may not correspond to any values in the pivot column of the table. The column will be output, but will have null values. The following SQL batch shows this. SELECT * FROM SALES PIVOT (SUM (Amount) FOR [Quarter] IN (Q2, LastQ)) As P GO Year Q2 LastQ – 2001 190 NULL 2002 250 NULL Note that the Quartercolumn of the SALEStable has no value LastQ , so the output of the PIVOTlists all the values in the LastQcolumn as NULL. Using PIVOT for Open Schemas Using PIVOTfor an open schema is really no different from using PIVOTfor analysis, except that we don t depend on PIVOT s ability to aggregate a result. The open schema has two tables, a Product table and a Properties

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

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

T-SQL ENHANCEMENTS INSERT INTO SALES VALUES (2001, Q2 ,

T-SQL ENHANCEMENTS INSERT INTO SALES VALUES (2001, Q2 , 70) INSERT INTO SALES VALUES (2001, Q3 , 55) INSERT INTO SALES VALUES (2001, Q3 , 110) INSERT INTO SALES VALUES (2001, Q4 , 90) INSERT INTO SALES VALUES (2002, Q1 , 200) INSERT INTO SALES VALUES (2002, Q2 , 150) INSERT INTO SALES VALUES (2002, Q2 , 40) INSERT INTO SALES VALUES (2002, Q2 , 60) INSERT INTO SALES VALUES (2002, Q3 , 120) INSERT INTO SALES VALUES (2002, Q3 , 110) INSERT INTO SALES VALUES (2002, Q4 , 180) GO To get a view that is useful for quarter-over-year comparisons, we want to pivot the table s Quarter column into a row heading and aggregate the sum of the values in each quarter for a year. The SQL batch that follows shows a PIVOTcommand that does this. SELECT * FROM SALES PIVOT (SUM (Amount) Aggregate the Amount column using SUM FOR [Quarter] Pivot the Quarter column into column headings IN (Q1, Q2, Q3, Q4)) use these quarters AS P GO Year Q1 Q2 Q3 Q4 – – – – 2001 100 190 165 90 2002 200 250 230 180 The SELECT statement selects all the rows from SALES. A PIVOT clause is added to the SELECT statement. It starts with the PIVOT keyword followed by its body enclosed in parentheses. The body contains two parts separated by the FOR keyword. The first part of the body specifies the kind of aggregation to be performed. The argument of the aggregate function must be a column name; it cannot be an expression as it is when an aggregate function is used outside a PIVOT. The second part specifies the pivot column that is, the column to pivot into a row and the values from that column to be used as column headings. The value for a particular column in a row is the aggregation of the column specified in the first part, over the rows that match the column heading. Note that it is not required to use all the possible values of the pivot column. You only need to specify the Q2 column if you wish to analyze just the year-over-year Q2 results. The SQL batch that follows shows this.

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

T-SQL LANGUAGE ENHANCEMENTS AttachIt Table Swish Table Product

T-SQL LANGUAGE ENHANCEMENTS AttachIt Table Swish Table Product Table Properties Table Table per Product Open Schema Figure 7-7: Tables for Hardware Store table named Swish with columns for quantity, color, and type. This, of course, requires products and their attributes to be known and for those attributes to remain constant over time. What happens if the manufacturer of the Swish paint adds a new attribute, Drying Time , but only to certain colors of paint? An alternate solution is to have only two tables, regardless of the number of products involved or the attributes they have. In the case of the hardware store, there would be a Product table and a Properties table. The Product table would have an entry per product, and the Properties table would contain the arbitrary attributes of that product. The properties of a product are linked to it via a foreign key. This is called an open schema. Figure 7-7 shows the two ways of designing tables to represent the inventory of the hardware store. The PIVOT operator can easily convert data that is stored using an open schema to a view that looks the same as the table-per-product solution. Next, we will look at the details of using PIVOT to analyze data and support open schemas, and then how to use PIVOT to work with open schemas. There is also an UNPIVOToperator, which can be used to produce the original open schema format from previously pivoted results. Using PIVOT for Analysis In this example, we are going to use PIVOT to analyze the sales data we showed in an earlier table. To do this, we build a SALEStable and populate it with data, as is shown in the following SQL batch. CREATE TABLE SALES ( [Year] INT, Quarter CHAR(2), Amount FLOAT ) GO

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

T-SQL ENHANCEMENTS Table 7-5: Individual Sales, Including Quarter

T-SQL ENHANCEMENTS Table 7-5: Individual Sales, Including Quarter of Sale Year Quarter Amount 2001 Q1 100 2001 Q2 120 2001 Q2 70 2001 Q3 55 2001 Q3 110 2001 Q4 90 2002 Q1 200 2002 Q2 150 2002 Q2 40 2002 Q2 60 2002 Q3 120 2002 Q3 110 2002 Q4 180 The hardware store sells Swish brand paint that has attributes of quantity, color, and type. It also sells AttachIt fastener screws, and these have attributes of pitch and diameter. Over time, it expects to add many other products to its inventory. With this categorization Swish, 1 qt, green, latex would be one product or entity, and Swish, 1qt, blue, oil would be another. A classic solution to designing the database the hardware store will use to maintain its inventory is to design a table per product. For example, a Table 7-6: Yearly Sales Broken Down by Quarter Year Q1 Q2 Q3 Q4 2001 100 190 165 90 2002 200 250 230 180

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

T-SQL LANGUAGE ENHANCEMENTS SELECT B.* S.MinLength, S.MaxLength FROM

T-SQL LANGUAGE ENHANCEMENTS SELECT B.* S.MinLength, S.MaxLength FROM Belt AS B CROSS APPLY Stretch(B.Length) AS S GO B10, 6, 15 B20, 16, 25 B30, 26, 35 B40, 36, 45 B50, 46, 55 The preceding example could have been done using CROSS and OUTER JOIN. CROSSAPPLYis required, however, when used in conjunction with XML data types in certain XML operations that will be discussed in Chapter 9. PIVOT Command SQL Server 2005 adds the PIVOT command to T-SQL, so named because it can create a new table by swapping the rows and columns of an existing table. PIVOT is part of the OLAP section of the SQL:1999 standard. There are two general uses for the PIVOTcommand. One it to create an analytical view of some data, and the other is to implement an open schema. A typical analytical use of the PIVOT command is to covert temporal data into categorized data in order to make the data easier to analyze. Consider a table used to record each sale made as it occurs; each row represents a single sale and includes the quarter that indicates when it occurred. This sort of view makes sense for recording sales but is not easy to use if you want to compare sales made in the same quarter, year over year. Table 7-5 lists temporally recorded sales. You want to analyze same- quarter sales year by year from the data in the table. Each row represents a single sale. Note that this table might be a view of a more general table of individual sales that includes a date rather than a quarterly enumeration. The PIVOT command, which we will look at shortly, can convert this temporal view of individual sales into a view that has years categorized by sales in a quarter. Table 7-6 shows this. Presenting the data this way makes it much easier to analyze same- quarter sales. This table aggregates year rows for each given year in the previous table into a single row. However, the aggregated amounts are broken out into quarters rather than being aggregated over the entire year. The other use of the PIVOTcommand is to implement an open schema. An open schema allows arbitrary attributes to be associated with an entity. For example, consider a hardware store; its entities are the products that it sells. Each product has a number of attributes used to describe it. One common attribute of all products it the name of the product.

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

T-SQL ENHANCEMENTS length FLOAT ) GO fill

T-SQL ENHANCEMENTS length FLOAT ) GO fill table with some data DECLARE @index INT SET @index = 5 WHILE(@index > 0) BEGIN INSERT INTO BELT VALUES ( B + CONVERT(VARCHAR, @index), 10 * @index) SET @index = @index 1 END GO make a table-valued function CREATE FUNCTION Stretch (@length FLOAT) RETURN @T TABLE ( MinLength FLOAT, MaxLength FLOAT ) AS BEGIN IF (@length > 20) INSERT @T VALUES (@length 4, @length + 5) RETURN END GO SELECT B.* S.MinLength, S.MaxLength FROM Belt AS B CROSS APPLY Stretch(B.Length) AS S GO B30, 26, 35 B40, 36, 45 B50, 46, 55 The rows in the Belt table are cross-applied to the Stretch function. This function produces a table with a single row in it if the @lengthparameter passed into it is greater than 20; otherwise, it produces a table with no rows in it. The CROSS APPLY operator produces output when each table involved in the CROSS APPLY has at least one row in it. It is similar to a CROSS JOINin this respect. OUTER APPLYis similar to OUTER JOINin that it produces output for all rows involved in the OUTER APPLY. The following SQL batch shows the results of an OUTER APPLY involving the same Belt table and Stretch function as in the previous example.

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

T-SQL LANGUAGE ENHANCEMENTS will be aborted. You can

T-SQL LANGUAGE ENHANCEMENTS will be aborted. You can control this with an OPTION(MAXRECURSION 10), for example, to limit recursion to a depth of 10. The example that follows shows its usage. WITH Rollup(id, parent, balance) AS ( body of CTE removed for clarity ) SELECT id, SUM(balance) balance FROM Rollup GROUP BY id OPTION (MAXRECURSION 10) GO APPLY Operators T-SQL adds two specialized join operators: CROSSAPPLYand OUTERAPPLY. Both act like a JOINoperator in that they produce the Cartesian product of two tables except that no ON clause is allowed. The following SQL batch is an example of a CROSSAPPLYbetween two tables. CREATE TABLE T1 ( ID int ) CREATE TABLE T2 ( ID it ) GO INSERT INTO T1 VALUES (1) INSERT INTO T1 VALUES (2) INSERT INTO T2 VALUES (3) INSERT INTO T2 VALUES (4) GO SELECT COUNT(*) FROM T1 CROSS APPLY T2 The APPLYoperators have little utility with just tables or views; a CROSS JOINcould have been substituted in the preceding example and gotten the same results. It is intended that the APPLY operators be used with a table- valued function on their right, with the parameters for the table-valued function coming from the table on the left. The following SQL batch shows an example of this. CREATE TABLE Belt ( model VARCHAR(20),

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

T-SQL ENHANCEMENTS id balance – 2001 14 2001

T-SQL ENHANCEMENTS id balance – 2001 14 2001 12 2001 10 The balances 14, 12, and 10 correspond to the balances in the detail accounts 3001, 4001, and 4002, which are all decedents of account 2001. The query that follows the body of the CTE then groups the rows that are produced by account ID and calculates the balance with the SUMfunction. There are other ways to solve this problem without using CTEs. A batch that uses a stored procedure that calls itself or a cursor could produce the same result. However, the CTE is a query, and it can be used to define a view, something a stored procedure or a cursor-based batch cannot. The view definition that follows defines a view, which is the recursive query we used earlier, and then uses it to get the balance for a single account, account 2001. CREATE VIEW Rollup AS WITH Rollup(id, parent, balance) AS ( SELECT id, parent, balance FROM DetailAccount UNION ALL SELECT R1.id, R1.parent, R2.balance FROM RollupAccount R1 JOIN Rollup R2 ON R1.id = R2.parent ) SELECT id, SUM(balance) balance FROM Rollup GROUP ID id GO get the balance for account 2001 SELECT balance FROM rollup WHERE id = 2001 GO balance 36 (1 row(s) affected) One of the strengths of a recursive query is the fact that it is a query and can be used to define a view. In addition, a single query in SQL Server is always a transaction, which means that a recursive query based on a CTE is a transaction. Recursive queries, like any recursive algorithm, can go on forever. By default, if a recursive query attempts to do more than 100 recursions, it

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

T-SQL LANGUAGE ENHANCEMENTS means that our anchor must

T-SQL LANGUAGE ENHANCEMENTS means that our anchor must select all the detail accounts, and the recursive calls must progressively walk up the hierarchy to account 1000. Note that there is no requirement that the anchor produce a single row; it is just a SELECTstatement. The query that follows produces the values of all the accounts, both detail and rollup. WITH Rollup(id, parent, balance) AS ( anchor SELECT id, parent, balance FROM DetailAccount UNION ALL recursive call SELECT R1.id, R1.parent, R2.balance FROM RollupAccount R1 JOIN Rollup R2 ON R1.id = R2.parent ) SELECT id, SUM(balance) balance FROM Rollup GROUP BY id GO id balance – 1000 104 2001 36 2002 52 2003 16 3001 10 3002 26 3004 17 3005 10 3006 25 3007 7 3008 9 4001 12 4002 14 (13 row(s) affected) This query starts by having the anchor select all the detail accounts. The recursive call selects all the accounts that are parents, along with any balance produced by the previous call. This results in a table in which accounts are listed more than once. In fact, the table has as many rows for an account as that account has descendant accounts that are detail accounts. For example, if you looked at the rows produced for account 2001, you would see the three rows shown in the following diagram.

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