Side-By-Side Lists
July 19, 2016 Hey, Ted
|
An end user has asked me to provide him a spreadsheet with two independent lists, one beside the other. Can I use SQL to satisfy his request? –Dennis Yes, you can. Dennis works for a manufacturer, and his data had to do with routing operations. I’m going to use a more common type of data to illustrate. Let’s say we work for a company that sells dohickeys, thingamajigs, and whatchamacallits. We offer these items in various colors, but we don’t offer all items in all colors.
We offer the items in various sizes, but we don’t offer all items in all sizes.
Color and size are not related to one another. That is, we don’t offer, for example, medium whatchamacallits in red and purple, but large whatchamacallits in red, purple, and green. If we did, the data would be stored differently in the tables. To build side-by-side lists is easy if you use the ROW_NUMBER OLAP function. Here’s the query:
with f1 as
(select Item, Color,
row_number() over (partition by Item) as RowNbr
from ItemColors),
f2 as
(select Item, Size,
row_number() over (partition by Item) as RowNbr
from ItemSizes)
select coalesce(f1.Item, f2.Item), f1.color, f2.size
from f1 full outer join f2
on (f1.item, f1.rownbr) = (f2.item, f2.RowNbr)
order by 1
And here’s the result set:
The blank colors and sizes are null values. So how does this work? First, the common table expressions use the ROW_NUMBER function to number the rows. Here are the colors:
select Item, Color,
row_number() over (partition by Item) as RowNbr
from ItemColors
ROW_NUMBER numbers the rows. Think of PARTITION BY as a control break. Thanks to PARTITION BY, numbering restarts with each item. It works the same way for sizes.
select Item, Size,
row_number() over (partition by Item) as RowNbr
from ItemSizes
To build the side-by-side lists is a simple matter of joining the two common table expressions on item and row number. Since an item may be offered in more or fewer colors than sizes, this query requires a full outer join.
select coalesce(f1.Item, f2.Item), f1.color, f2.size
from f1 full outer join f2
on (f1.item, f1.rownbr) = (f2.item, f2.RowNbr)
order by 1
This is not a common query, and I would not call the result set relational. However, our job is to help users do their jobs, not to be purists. Ted Holt welcomes your comments and questions. Email him through the IT Jungle Contacts page.
|
