Did you not read my first post????

As I said, this is coming from a View.

The table is to simplify the question. So no PK and all the Joins were...

I was able to get this to work using combination of Rank, Row_Number.

I also made sure that the totals were on the bottom using a Case Statement.

Here is what I...

I figured out part of the problem. I can only have Country, State and City in the Group By Clause.

So I can now just keep adding each aggregated column.

SELECT...

Looks good.

Thanks,

Tom

Hi Chris,

One other small question.

With the Cross Join there is "ON" part.

What if the Model table had a column "IsActive" which is a 0 or 1 and I wanted only...

I got it to work with the new scenario. I wasn't sure how to deal with multiple mapping tables but realized that it really is no different.

For the new...

I agree and have been putting a different schema and data that is really close to what I am trying to actually do.

The other examples helped me to better see...

Hi Cadavre,

No problem.

I understand what Chris had explained about the CROSS JOIN. I was trying apply that to the situation I have which I couldn't make work so I...

Actually, it isn't exactly the same way.

I was trying to figure out how to get your way to work with a middle table and couldn't get it to work.

You're way...

That worked really good.

But I couldn't get it to work with a many to many table in the middle

Here is the schema:

CREATE TABLE [dbo].[Dealers](

[DealerID] [int] NULL,

[DealerCode] [varchar](15) NOT...

I made some changes and created a couple of tables to show the issue.

Either way (change where clause or Join with the test for the DealerCode) solves the first...

Actually, this is a stripped down version of the actual query I was using so it is a little difficult to do that.

But I did figure out my issue, just...

Actually, the topic should say RIGHT JOIN and not LEFT JOIN.

Actuall, Celko, I am not formatting the data on the back end, other than adding a comma to separate the data.

I am getting a summary of child records that the...

That worked great, Okbanas.

I actually used this to create a subquery and surround it with a STUFF to get rid of the 1st comma.

Thanks,

Tom