SQL Server Join Question

MEG_AGI 

There is a problem with the data in terms of how the records are associated. I visually understand that you would like the first rep to appear to the right and the second rep appear on the left as the other rep. It is a common problem when working with set of data. The First and Other need to have a very specific designation. For example an additional column can be added like rep_type that can have the value Primary or Other.

So the data would look like 

State  County    Rep     Rep Type Amount

01        001        100     Primary    $300.00

01        001        999     Other       $  22.50

01        002        300     Primary    $750.00

01        002        999     Other       $  12.75

02        020        400     Primary    $450.00

02        020        999     Other       $  57.00

02        030        111     Primary    $645.00

Now the table contains two things. Primary Reps and Other Reps. Your data table is a deviated from the spirit of relational database design but this can still work. Basically the rows in the table should be rows of the same thing (related) but in fact they are records of Reps and Other Reps.

Select each type of rep from the table with additional zero value or blank columns for the other type. Union the results together, then group by State, County, max Rep, max Amount, max Other Rep, max Other Amount.

The Query looks like this: 

select A.[State],
       A.[Country],
    max(A.[Rep]) as [Rep],
    max(A.[Amount]) as [Amount],
    max(A.[Other Rep]) as [Other Rep],
    max(A.[Other Amount]) as [Other Amount]
from  (SELECT [State],
              [Country],
     [Rep],
     [Amount],
     '' as [Other Rep],
     0  as [Other Amount]
        FROM [IN_STAGING_dev].[dbo].[Test_Table]
       where [Rep Type] = 'Primary'
      UNION
      SELECT [State],
          [Country],
    '' as [Rep],
    0  as [Amount],
    [Rep] as [Other Rep],
    [Amount]  as [Other Amount]
        FROM [IN_STAGING_dev].[dbo].[Test_Table]
       where [Rep Type] = 'Other') A
 group by A.[State],
          A.[Country]

And the result looks like this: 

State     Country  Rep        Amount Other Rep Other Amount
01         001         100        300.00    999           22.50
01         002         300        750.00    999           12.75
02         020         400        450.00   999            57.00
02         030         111        645.00                     0.00

I suggest that you create a primary key that contains State, Country and Rep Type. This means a record represents One type of Rep (Either Rep or other rep per a state and County) This way the query will always produce the correct result.

Have a nice day.