loading..
Русский    English
17:40
go

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
  1. SELECT name, value 
  2. FROM (
  3. VALUES('speed', 1)
  4. ,('ram', 1)
  5. ,('hd', 1)
  6. ,('screen', 1)
  7. ) 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
  1. SELECT code, name, value 
  2. FROM Laptop
  3. CROSS APPLY (
  4. VALUES('speed', 1)
  5. ,('ram', 1)
  6. ,('hd', 1)
  7. ,('screen', 1)
  8. ) Spec(name, value)
  9. WHERE code < 4 -- for reducing the result set only
  10. ;

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
  1. SELECT code, name, value FROM Laptop
  2. CROSS APPLY
  3. (VALUES('speed', speed)
  4. ,('ram', ram)
  5. ,('hd', hd)
  6. ,('screen', screen)
  7. ) spec(name, value)
  8. WHERE code < 4 -- for reducing the result set only
  9. ORDER BY code, name, value;

Suggested exercises: 97, 117, 132, 145

Bookmark and Share
Pages 1 2 3
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.