I can't believe I'm asking this, but here it is: We have a normalized database where somebody insists that it would be better if it were somewhat de-normalized. Everybody else thinks that's a bad idea, and it's not going to happen, but I thought there might be an intermediate solution to have it both ways, and that's what this question is about.

Suppose two tables:

Table 1:

PK
SomeStuff
SomeCount

Table 2:
PK
FK
OtherStuff
MoreOtherStuff.

If I had a query with a LEFT OUTER JOIN, it could look like this:

SELECT SomeStuff,SomeCount,OtherStuff,MoreOtherStuff
From Table1 LEFT OUTER JOIN Table 2 ON Table1.PK = Table2.FK

This will do almost exactly what the person who wants the tables denormalized wants, with one exception.

The SomeCount field in Table 1 could be some integer of 1 or greater. Quite often, if the count is > 1, then there will be no records in Table 2, but there MIGHT be. If I could say for certain that whenever SomeCount > 1, then there will be no record in table 2, then the LEFT OUTER JOIN query would return only one record for each record in Table 1, and a Sum of SomeCount will be correct.

I can't say that, though, so SomeCount could be 3, and the LEFT OUTER JOIN query might result in two or three rows, each with SomeCount = 3, and with whatever data from the two or three rows in Table 2. That would mean that a Sum of SomeCount would be different from just summing the SomeCount in Table 1. In other words:

SELECT SUM(SomeCount) FROM Table1

Would not return the same value as:

SELECT SUM(SomeCount)
From Table1 LEFT OUTER JOIN Table 2 ON Table1.PK = Table2.FK

This would bother me not at all, but it may bother the one guy who insists on denormalizing the database. So, what I'm asking is whether or not there is some reasonable approach that would essentially 'unroll' the case where the LEFT OUTER JOIN results in more than one record?

That's a more difficult problem than it might appear at first, so I don't think there is a REASONABLE way to do this (and it's too stupid a need to waste any time on, when it could be solved so much easier through code). The issue is that if SomeCount > 1, then there need not be SomeCount records in Table 2. In other words, if SomeCount were 5, there could be 3 records in Table 2, so the unrolled view would have to end up looking like this:

SomeStuff,1,OtherStuff,MoreOtherStuff
SomeStuff,1,OtherStuff,MoreOtherStuff
SomeStuff,1,OtherStuff,MoreOtherStuff
SomeStuff,2,Null,Null

Where that last record has a count of 2, as there are two records with no matching records in Table 2.

Is there a simple way to do this?