Press "Enter" to skip to content

Grouping groups of strings within strings in TSQL.

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.

2 Comments

  1. 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?

    • ck ck

      Yep, you got it.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.