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

Bookmark the permalink.

Comments are closed.