{"id":157,"date":"2007-11-09T15:05:00","date_gmt":"2007-11-09T15:05:00","guid":{"rendered":"\/lisa\/post\/2007\/11\/09\/SQL-for-Sideways-Layout-Reports.aspx"},"modified":"2007-11-09T15:05:00","modified_gmt":"2007-11-09T15:05:00","slug":"sql-for-sideways-layout-reports","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2007\/11\/09\/sql-for-sideways-layout-reports\/","title":{"rendered":"SQL for Sideways-Layout Reports"},"content":{"rendered":"

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

\nThere 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<\/a>, 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.\n<\/p>\n

\nI posted a sample solution using completely in-RDL techniques, without manipulating the data, in an RS forum thread<\/a>  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<\/a>.\n<\/p>\n

\nIf 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<\/a> 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.\n<\/p>\n

\nSo 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?\n<\/p>\n

\nI'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.\n<\/p>\n

\nThere 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.\n<\/p>\n

\nReady?\n<\/p>\n

1. Start with a basic query.<\/h3>\n

\nI 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.\n<\/p>\n

<\/p>\n

\nSELECT <\/font>Name<\/font> <\/font>FROM<\/font> City <\/font>ORDER<\/font> <\/font>BY<\/font> <\/font>Name<\/font>\n<\/p>\n

<\/font><\/font><\/p>\n

\n— result, 4079 rows :
\n[San Cristóbal de] la Laguna      
\n´s-Hertogenbosch                  
\nA Coruña (La Coruña)              
\nAachen                            
\n:
\nZwolle                            
\nZytomyr                            <\/font>\n<\/p>\n

\nAs 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.\n<\/p>\n

2. How many rows?<\/h3>\n

\nWe need to know what the row number is for each row, for later use.\n<\/p>\n

<\/p>\n

\nSELECT Name<\/font>,<\/font> ROW_NUMBER<\/font>()<\/font> OVER<\/font> (<\/font>ORDER<\/font> BY<\/font> Name<\/font>)<\/font> FROM<\/font> City\n<\/p>\n

<\/font><\/p>\n

\n— result:
\n[San Cristóbal de] la Laguna        1
\n´s-Hertogenbosch                    2
\nA Coruña (La Coruña)                3
\n:
\nZwolle                              4078
\nZytomyr                             4079 <\/font>\n<\/p>\n

\nWe also need to have a third as many rows as we have cities (rounding up). \n<\/p>\n

<\/p>\n

\nSELECT RowNo FROM<\/font>
\n(<\/font>SELECT<\/font> ROW_NUMBER<\/font>()<\/font>
\n   OVER<\/font> (<\/font>ORDER<\/font> BY<\/font> Name<\/font>)<\/font> AS<\/font> RowNo FROM<\/font> City)<\/font> A
\nWHERE<\/font> (<\/font>RowNo <=<\/font> (<\/font>SELECT<\/font> CEILING<\/font>(<\/font>CAST<\/font>(<\/font>COUNT<\/font>(*)<\/font> AS<\/font>
\n             Numeric<\/font>(<\/font>10,<\/font>2))\/<\/font>3)<\/font> FROM<\/font> City))
\n<\/font>\n<\/p>\n

<\/font><\/p>\n

\n— result: 1 through 1360\n<\/p>\n

\nNotice 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.\n<\/p>\n

3. What cities belong in the first column?<\/h3>\n

\nWe 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:\n<\/p>\n

<\/p>\n

\nSELECT <\/font>Name<\/font> <\/font>AS<\/font> Col1 <\/font>FROM<\/font>
\n<\/font>  (<\/font>SELECT<\/font> <\/font>Name<\/font>,<\/font> <\/font>ROW_NUMBER<\/font>()<\/font> <\/font>OVER<\/font> <\/font>(<\/font>ORDER<\/font> <\/font>BY<\/font> <\/font>Name<\/font>)<\/font> <\/font>AS<\/font> ImageNo <\/font>FROM<\/font> City<\/font>)<\/font> X1
\n<\/font>    WHERE<\/font> (<\/font>ImageNo %<\/font> 3 =<\/font> 1)<\/font>\n<\/p>\n

<\/font><\/p>\n

\n— result, 1360 rows:
\n[San Cristóbal de] la Laguna      
\nAachen                            
\nAbadan                            
\n:
\nZunyi                             
\nZwolle                             <\/font>\n<\/p>\n

<\/font><\/p>\n

\nObviously, 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).\n<\/p>\n

4. Put it together<\/h3>\n

\nWe 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:\n<\/p>\n

<\/p>\n

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

<\/font><\/p>\n

\n–result:<\/p>\n

1 [San Cristóbal de] la Laguna      
\n2 Aachen                            
\n3 Abadan                            
\n:
\n1358 Zonguldak                         
\n1359 Zunyi                             
\n1360 Zwolle<\/font>                            \n<\/p>\n

<\/font><\/font><\/p>\n

\n… 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:\n<\/p>\n

<\/p>\n

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

<\/font><\/p>\n

\n— result:RowNo Col1 Col2 Col3
\n1 [San Cristóbal de] la Laguna    ´s-Hertogenbosch  A Coruña (La Coruña)              
\n2 Aachen                          Aalborg           Aba                               
\n3 Abadan                          Abaetetuba        Abakan                            
\n4 Abbotsford                      Abeokuta          Aberdeen                          
\n5 Abha                            Abidjan           Abiko                             
\n:
\n1359 Zunyi                        Zürich            Zwickau                           
\n1360 Zwolle                       Zytomyr           NULL <\/font>\n<\/p>\n

\nMaking 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.<\/p>\n","protected":false},"excerpt":{"rendered":"

(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"Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,6],"tags":[],"class_list":["post-157","post","type-post","status-publish","format-standard","hentry","category-reporting","category-sql-server"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/157","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/comments?post=157"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/157\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=157"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=157"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=157"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}