SQL Server Join Question

[MEG_AGI]

I'm new to the forum so if this is posted in the wrong place, please point me to the correct spot.

I have a table that has the following columns:

State  County    Rep     Amount

01        001        100       $300.00

01        001        999       $  22.50

01        002        300       $750.00

01        002        999       $  12.75

02        020        400        $450.00

02        020        999        $  57.00

02        030        111        $645.00

All other rep data is on a separate row with a code of 999.

What I need to do is create output that looks like this:

State  County    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            $  0.00

In other words, I want to place the other rep data beside the Rep data in the table so that there is only 1 row per State/County Combo.

It seems simple but as a novice I need some help.

I appreciate any assistance.

MEG_AGI

[#2]

@MEG_AGI 

Please use below query

Drop TABLE IF EXISTS #temp
Go
Drop TABLE IF EXISTS #tempfinal
GO

Create table #temp
(
State varchar(10),
County varchar(10),
Rep int,
Amount varchar(100)
)
insert into #temp
select '01','001','100','$300.00' union
select '01','001','999','$22.50' union
select '01','002','300','$750.00' union
select '01','002','999','$12.75' union
select '02','020','400',' $450.00' union
select '02','020','999',' $57.00' union
select '02','030','111',' $645.00'

Alter table #temp add [OtherRep] int,[Other Amount] varchar(200)

;with CTE as (
select *,RANK()over(partition by State,County order by state,County,Rep) as Rnk
from #temp)

select C1.State,C1.County,C1.Rep,C1.Amount,C2.[Rep][other Rep],C2.[Amount] as [Other Amount]
from CTE as C1
inner join CTE as C2 on C2.State=C1.State and C2.county=C1.County AND C2.Rnk=2
where C1.Rnk=1

[#3]

@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.