So a friend of mine had a query puzzle – he needed to print some W2 forms but the forms themselves only allows up to 4 groups of a value to be placed in a box or else they’d need to issue two forms.
The pickle is that the source data is freeform csv text, so what can we do on the database side?
I reached for my handy CROSS APPLY, a numbers table, windowing functions and a little modulo arithmetic – its not so bad to return a subgroup within a group in SQL as long as you are willing to lay out additional columns to count your grouping.
This is gnarly.
I’m trying to understand what the actual problem was again (any time I see these puzzles, I like to figure out my own solution to see what I come up with).
So you have a couple records, each with a list of things…
So if you have 1 row with 7 items
And you have another row with 11 items
The goal is to end up with (11+7) / 4 rows? aka 5 rows with a max of 4 items per row?
Yep, you got it.