Why is this SQL returning 15 rows instead of 12?

Question

I need to display data from the database in a "spreadsheety" type of way, with values being displayed vertically instead of horizontally as rows, as is normally the case.

The grid needs to be exactly 16 columns by 12 rows. In the normal scenario, it would be two columns, with up to 96 rows.

The query I derived from ClearLogic's answer here:

Is there a SQL statement that will break what would be 2 long columns into several pairs of columns?

..is tantalizingly close, but it's generating 15 columns, and adding some values twice. It looks close enough that it makes me want to continue down this path.

Note: Val1 is always a value between 1..96

My sql (which produces a 16 column by 15 row result set) is:

Select Val1 AS col1, Val2 AS col2,NULL AS Col3,NULL AS Col4 ,NULL AS 
Col5,NULL AS Col6,NULL AS col7,NULL AS Col8, NULL AS Col9, NULL AS Col10, NULL AS 
Col11, NULL AS Col12, NULL AS Col13, NULL AS Col14, NULL AS Col15, NULL AS Col16 
from PLATYPUSAVAILABILITY where Val1 < 13 and PLATYPUSID = 42
AND AVAILABLEDATE = to_date('20120823', 'yyyymmdd')

UNION select NULL AS col1,NULL AS col2,Val1 AS Col3, Val2 AS Col4,NULL 
AS Col5,NULL AS Col6,NULL AS col7,NULL AS Col8, NULL AS Col9, NULL AS Col10, NULL AS
Col11, NULL AS Col12, NULL AS Col13, NULL AS Col14, NULL AS Col15, NULL AS Col16 
from PLATYPUSAVAILABILITY where Val1 > 12 and Val1 < 25 and 
PLATYPUSID = 42 AND AVAILABLEDATE = to_date('20120823', 'yyyymmdd')

UNION select NULL AS col1,NULL AS col2 ,NULL AS Col3, NULL AS Col4,Val1 AS 
Col5, Val2 AS Col6,NULL AS col7,NULL AS Col8, NULL AS Col9, NULL AS Col10, NULL 
AS Col11, NULL AS Col12, NULL AS Col13, NULL AS Col14, NULL AS Col15, NULL AS Col16 
from PLATYPUSAVAILABILITY where Val1 > 24 and Val1 < 37 and 
PLATYPUSID = 42 AND AVAILABLEDATE = to_date('20120823', 'yyyymmdd')

UNION select NULL AS col1,NULL AS col2 ,NULL AS Col3, NULL AS Col4, NULL AS 
Col5, NULL AS Col6,Val1 AS col7,Val2 AS Col8, NULL AS Col9, NULL AS 
Col10, NULL AS Col11, NULL AS Col12, NULL AS Col13, NULL AS Col14, NULL AS Col15, 
NULL AS Col16 from PLATYPUSAVAILABILITY where Val1 > 36 and Val1 
< 49 and PLATYPUSID = 42 AND AVAILABLEDATE = to_date('20120823', 'yyyymmdd')

UNION select NULL AS col1,NULL AS col2 ,NULL AS Col3, NULL AS Col4,Val1 AS 
Col5,Val2 AS Col6,NULL AS col7,NULL AS Col8, Val1 AS Col9, Val2 AS 
Col10, NULL AS Col11, NULL AS Col12, NULL AS Col13, NULL AS Col14, NULL AS Col15, 
NULL AS Col16 from PLATYPUSAVAILABILITY where Val1 > 48 and Val1 
< 61 and PLATYPUSID = 42 AND AVAILABLEDATE = to_date('20120823', 'yyyymmdd')

UNION select NULL AS col1,NULL AS col2 ,NULL AS Col3, NULL AS Col4,NULL AS 
Col5,NULL AS Col6,NULL AS col7,NULL AS Col8, NULL AS Col9, NULL AS Col10, 
Val1 AS Col11, Val2 AS Col12, NULL AS Col13, NULL AS Col14, NULL AS 
Col15, NULL AS Col16 from PLATYPUSAVAILABILITY where Val1 > 60 and 
Val1 < 73 and PLATYPUSID = 42    AND AVAILABLEDATE = to_date('20120823', 'yyyymmdd')

UNION select NULL AS col1,NULL AS col2 ,NULL AS Col3, NULL AS Col4,NULL AS 
Col5,NULL AS Col6,NULL AS col7,NULL AS Col8, NULL AS Col9, NULL AS Col10, NULL AS 
Col11, NULL AS Col12, Val1 AS Col13, Val2 AS Col14, NULL AS Col15, NULL 
AS Col16 from PLATYPUSAVAILABILITY where Val1 > 72 and Val1 < 85 
and PLATYPUSID = 42 AND AVAILABLEDATE = to_date('20120823', 'yyyymmdd') 

UNION select NULL AS col1,NULL AS col2 ,NULL AS Col3, NULL AS Col4,NULL AS 
Col5,NULL AS Col6,NULL AS col7,NULL AS Col8, NULL AS Col9, NULL AS Col10, NULL AS 
Col11, NULL AS Col12, NULL AS Col13, NULL AS Col14, Val1 AS Col15, Val2 
AS Col16 from PLATYPUSAVAILABILITY where Val1 > 84 and PLATYPUSID = 42 
AND AVAILABLEDATE = to_date('20120823', 'yyyymmdd')

How can I get it to restrict itself to just 12 rows rather than overflowing into 15?

A screen shot of what I see after running this query in TOAD can be seen at:

http://warbler.posterous.com/so-close-and-yet-so-far-away-sqlizers-lament

There should be only 12 rows, and the values should start at the top of columns 9 and 10 and overflow onto one row (the top one) of columns 11 and 12.

I've used Datagridview as a tag because that's where this data is ultimately headed: datagridview1.DataSource = //query result as OracleDataTable

UPDATE

Got it working. See Is it possible to populate a DataGridView with alternating vertical columns?

I think you will need to post your source data to get an answer. Though I am wondering if you need to reformat 96 * 2 elements (192) into a 16 x 12 (192) element display, would it be simpler (to do and to maintain) to instead create a temporary table having 16 columns, and then insert very specific element sets into it? — John Bingham, Aug 22 '12 at 23:50
The data is: Val1 = 45..52, 54..60 while Val2 is all 3. IOW, 15 sets of values of the sort: [45,3],[46,3],[47,3]...exclude [53,X]...[59,3][60,3]. I'm beginning to think you're right: it was easier using the TableLayoutPanel... — B. Clay Shannon-B. Crow Raven, Aug 23 '12 at 00:17
Your query is returning 15 rows because the following query returns 15 rows (I guess): `select * from PLATYPUSAVAILABILITY where PLATYPUSID = 42 AND AVAILABLEDATE = to_date('20120823', 'yyyymmdd')`. If you want to do it this way there's no real need for all the `unions`. Just use `case when val`> 84 then val1 else null end` in a single select... — Ben, Aug 23 '12 at 07:18

score 1 · Accepted Answer · answered Aug 23 '12 at 03:32

I think the answer to the problem of why the display is wrong stems from your data. If you had rows with every possible value of Val1, with Val2 null for the rows where you do not currently have a val1, I would expect the display to be correct. I think the problem is arising because of the non-existence of rows which need (in my mind) to exist for the formatting to be correct.

I appreciate that this maynt be overly positive, as you may not always have all values of val1 anyway - but there is a way around this:

If you create a table of integers from 1 to 96, lets call this tblArray, then your from clauses can be:

 FROM tblArray t LEFT JOIN PlatypusAvailability p ON t.ID = p.Val1

& that should do the job. But I would suggest testing my thinking in the first paragraph first.

Why is this SQL returning 15 rows instead of 12?

UPDATE

1 Answers1