Feb 06 2019 12:02 PM
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
May 28 2019 05:51 AM - edited May 28 2019 05:54 AM
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
May 31 2019 08:09 AM
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.