3.14 Printing Matrices and Arrays

3.14.1 Problem

You want to print a matrix and an array.

3.14.2 Solution

Use the following pivoting technique, which, in this case, prints matrix D:

SELECT  X, 
   MAX(CASE Y WHEN 1 THEN Value END) y1,
   MAX(CASE Y WHEN 2 THEN Value END) y2,
   MAX(CASE Y WHEN 3 THEN Value END) y3
FROM Matrices
WHERE Matrix='D'
GROUP BY X
ORDER BY X

X           y1          y2          y3          
----------- ----------- ----------- ----------- 
1           3           4           5
2           5           6           7
3           8           9           0

3.14.3 Discussion

See the discussion on the use of Pivot tables in Chapter 1. Note particularly that the number of CASE expressions must match the Y dimension of the matrix. In this case, we know the matrix we want to print has three columns, so we wrote three CASE expressions.

Let's say that you want to print an array in a report-like fashion with each dimension in a separate column. In our example, you wish to print a report of purity levels for all containers in all production lines, and you wish each production line to be represented by its own column.

Use the same pivoting technique as used earlier in the recipe for printing matrices:

SELECT  ContainerId, 
   MAX(CASE Line WHEN 0 THEN Purity END) Line0,
   MAX(CASE Line WHEN 1 THEN Purity END) Line1,
   MAX(CASE Line WHEN 2 THEN Purity END) Line2,
   MAX(CASE Line WHEN 3 THEN Purity END) Line3
FROM ProductionFacility
GROUP BY ContainerId
ORDER BY ContainerId

ContainerId Line0       Line1       Line2       Line3       
----------- ----------- ----------- ----------- ----------- 
1           100         102         103         100
2           100         103         108         100
3           100         100         109         100