SQL for Sideways-Layout Reports

(I am supposed to be blogging about TMM and nothing else this month… so this will be VERY hastily done and I apologize in advance for any screwed up formatting.)

There is a classic report layout problem involving showing rows-as-columns that you might have heard described as the "snaking columns" problem, or the "label orientation" problem, or the "image proofsheet" problem. As I've mentioned before, I find it difficult to get the SQL Server RS matrix to fit these cases and I tend to rebuild the data to fit how it needs to be shown rather than use the matrix data region in some instances.

I posted a sample solution using completely in-RDL techniques, without manipulating the data, in an RS forum thread  which discussed this problem as "the sideways report". While I flatter myself that it's a really cool idea, in some situations you're better off handling the issue in SQL, and I want to illustrate that method here, in response to another forum query on the same subject.

If you have had to design a layout where data rows need to show up as columns, you probably know what I'm talking about and have your own variant of the scenario.  If you don't, when I get more time, I will come back and edit this post with more detail.  I will also try to come back and show how to extrapolate from this simple example to write the query dynamically, using some techniques that I've shown here in the past.  I didn't happen to post any examples in that post where JOINs or UNIONs are dynamically put into place based on a parameter, which is what is required in this case to build the SELECT string dynamically, but I do that in a number of reports and it's really just an extension of what I've already written in that earlier post.

So for now, I'll just illustrate using concrete SQL examples, where the number across is shown as 3.  To change the number 3 to a different number across you can either write dynamic SQL and do a bit of column-visibility expression work, or have separate reports, each using a different variant of this query appropriate to a different number of cells across.  OK?

I'll also, for now, use syntax that is SQL Server 2005 specific: the ROW_NUMBER() function.  Realize that there are ways of doing this in pre-2005 SQL Server, Oracle, etc, and I have posted some examples of this on forums in the past too.  When I have more time, if anybody queries this, I can add some additional code here.

There may also be a cool way to do this using CTEs and recursions, and maybe somebody will post here to scold me for not using it <g>.  I can only say: I can't think that through nearly as fast, and it will just be a variation on the same theme.  So perhaps we'll refine this post to do it better, even in SQL Server 2005-specific code, later as well.

Ready?

1. Start with a basic query.

I will use a table in the MySQL tutorial database for this example, because it's handy.  It's a table of cities.  In the example, we want to display the names of cities in an ersatz matrix format. Substitute whatever column you want.  In this example you'll see that I've referenced the city as ImageNo in a bunch of places — because the person who asked the question on the recent thread happens to be displaying images, not city names.

SELECT Name FROM City ORDER BY Name

— result, 4079 rows :
[San Cristóbal de] la Laguna      
´s-Hertogenbosch                  
A Coruña (La Coruña)              
Aachen                            
:
Zwolle                            
Zytomyr                           

As our starting conditions, we don't know how many cities there are in total, but we know we want 3 columns for this instance of the report.

2. How many rows?

We need to know what the row number is for each row, for later use.

SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) FROM City

— result:
[San Cristóbal de] la Laguna        1
´s-Hertogenbosch                    2
A Coruña (La Coruña)                3
:
Zwolle                              4078
Zytomyr                             4079

We also need to have a third as many rows as we have cities (rounding up). 

SELECT RowNo FROM
(SELECT ROW_NUMBER()
   OVER (ORDER BY Name) AS RowNo FROM City) A
WHERE (RowNo <= (SELECT CEILING(CAST(COUNT(*) AS
             Numeric(10,2))/3) FROM City))

— result: 1 through 1360

Notice that we have to CAST one of the divisions before the CEILING() function is applied; otherwise, given SQL Server's rule of least precision in an operation, we are going to lose a row for the "remainder" cities on a final, non-full, row.

3. What cities belong in the first column?

We need to determine which cities show in each column, which we can do with a Modulus operation.  Here's an example, for the first column:

SELECT Name AS Col1 FROM
  (SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) AS ImageNo FROM City) X1
    WHERE (ImageNo % 3 = 1)

— result, 1360 rows:
[San Cristóbal de] la Laguna      
Aachen                            
Abadan                            
:
Zunyi                             
Zwolle                            

Obviously, we can repeat this for the other two columns, changing the WHERE clause to show a different result in the Modulus operation; column 2 cities have a MOD result of 2, and column 3 cities have a MOD result of 0.  (When you do this dynamically, the last column is always the one with a MOD result of 0).

4. Put it together

We can join each row with the correct column 1 city to display as follows. Notice, again, that we will CAST the divisions.  Our WHERE clause is still here, limiting the number of rows to a third of the original total:

SELECT RowNo, Col1 FROM
   (SELECT ROW_NUMBER()
   OVER (ORDER BY Name) AS RowNo FROM City) A
LEFT JOIN
   (SELECT Name As Col1, ROW_NUMBER()
   OVER (ORDER BY Name) AS ImageNo FROM City) B
    ON B.ImageNo % 3 = 1 AND
   RowNo = CEILING(CAST(B.ImageNo AS Numeric(10,2))/3 )
WHERE (A.RowNo <=
   (SELECT CEILING(CAST(COUNT(*) AS Numeric(10,2))/3) FROM City))

–result:

1 [San Cristóbal de] la Laguna      
2 Aachen                            
3 Abadan                            
:
1358 Zonguldak                         
1359 Zunyi                             
1360 Zwolle                            

… and of course we can add joins for each additional column to get the full set — notice the NULL in the final column of the final, un-full row; this is exactly what we want where the numbers don't come out perfectly:

SELECT RowNo, Col1, Col2, Col3 FROM
   (SELECT ROW_NUMBER() OVER
   (ORDER BY Name) AS RowNo FROM City) A
LEFT JOIN
   (SELECT Name As Col1, ROW_NUMBER()
   OVER (ORDER BY Name) AS ImageNo FROM City) B
   ON B.ImageNo % 3 = 1 AND
   RowNo = CEILING(CAST(B.ImageNo AS Numeric(10,2))/3)
LEFT JOIN
   (SELECT Name As Col2, ROW_NUMBER()
   OVER (ORDER BY Name) AS ImageNo FROM City) C
   ON C.ImageNo % 3 = 2 AND
   RowNo = CEILING(CAST(C.ImageNo AS Numeric(10,2))/3)
LEFT JOIN
   (SELECT Name As Col3, ROW_NUMBER()
   OVER (ORDER BY Name) AS ImageNo FROM City) D
   ON D.ImageNo % 3 = 0 AND
   RowNo = CEILING(CAST(D.ImageNo AS Numeric(10,2))/3)
WHERE (A.RowNo <= (SELECT CEILING(CAST(COUNT(*)
                                 AS Numeric(10,2))/3 ) FROM City))

— result:RowNo Col1 Col2 Col3
1 [San Cristóbal de] la Laguna    ´s-Hertogenbosch  A Coruña (La Coruña)              
2 Aachen                          Aalborg           Aba                               
3 Abadan                          Abaetetuba        Abakan                            
4 Abbotsford                      Abeokuta          Aberdeen                          
5 Abha                            Abidjan           Abiko                             
:
1359 Zunyi                        Zürich            Zwickau                           
1360 Zwolle                       Zytomyr           NULL

Making this dynamic can be done, as usual, within T-SQL or VB code within a report and, as usual, there are better functions with which to perform the string manipulation in VB.