06:15

# CROSS APPLY / OUTER APPLY page 3

At last, let's consider an example of the task encountered frequently in practice, namely the task of putting values from rows in columns. To be specific, the formulation will be the following.

Deduce specifications from Laptop table in three columns: code; characteristic (speed, ram, hd, and screen); value of characteristic.

We'll use table constructor where the values of table columns will be passed in with aid of CROSS APPLY operator. Let's consider this method in details.

Table constructor can be used not only in INSERT statement but also for building the table in FROM clause of SELECT statement, for example,

Console
Execute
`SELECT name, value FROM (VALUES('speed', 1),('ram', 1),('hd', 1),('screen', 1)) Spec(name, value);`

This table in our example is named Spec and includes two columns - name (character strings) and value (numeric).

Now we use this table in CROSS APPLY operator which will combine each row in Laptop table with four rows from such generated table:

Console
Execute
`SELECT code, name, value FROM LaptopCROSS APPLY (VALUES('speed', 1),('ram', 1),('hd', 1),('screen', 1)) Spec(name, value)WHERE code < 4 -- for reducing the result set only;`

Actually, we need to use the main property of CROSS APPLY operator - using correlated subquery - and to replace units in the value column by column names of the table to be joined.

Console
Execute
`SELECT code, name, value FROM LaptopCROSS APPLY(VALUES('speed', speed),('ram', ram),('hd', hd),('screen', screen)) spec(name, value)WHERE code < 4 -- for reducing the result set onlyORDER BY code, name, value;`

Suggested exercises: 97, 117, 132145

 Pages 1 2 3