Combine data from two tables

[runmd]

I have two tables of data with a county FIPS identifier. For anyone that does not know, it is comprised of a 2-digit state FIPS code and a 3-digit county FIPs code to create a unique identifier for each county. I have records where I have one per county or in some cases two per county in one table. The other table may have records with the same situation. I want to join the two tables together based on the county and use multiple columns to store one or more cases for each county. Is this doable?

 

[LKBrwnDBA]

Yes.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Andrzejek]

LKBrwnDBA answered your question, but I would guess you will not be very happy with it.

Since you are new to this forum I would suggest to you to to read How Do I Get Great Answers To my Tek-Tips Questions? and How Do I Get Great Answers To my Tek-Tips Questions? before you post a question.

I know that you know your problem, you just have to state it the way so we will know what your problem is in order to help you.

Have fun.

---- Andy

 

[runmd]

Well, if I learned one thing from that link it is to be nice.

Perhaps I should rephrase the question. I have two tables and both have a 5-digit county FIPS code, county name, a 5 character unique code and a count.

Table 1:

FIPS  County Name  Code   Count
01003  Baldwin     AL001   50
01003  Baldwin     AL003   4
01097  Mobile      AL004   12
01097  Mobile      AL006   26

Table 2:

FIPS  County Name  Code   Count
01003  Baldwin     AL001   10
01003  Baldwin     AL061   5
01097  Mobile      AL004   6
01097  Mobile      AL006   2
01097  Mobile      AL007   25

I'm looking for a way to join the two tables together so that I have the county code, county name, unique 5 character codes and add up all of the counts. I want to know which codes are in what counties and add up how many from the count field in this manner:

Output Table:

FIPS  County Name  Code1  Code2  Code3   Counts
01003  Baldwin     AL001  AL003  AL061     69
01097  Mobile      AL004  AL006  AL007     71

This should be easy enough to do but I'm not able to create a SQL statement that can do it all in one shot. I've got a SQL statement that will create one table at a time, then you have to join all those tables together to get the output table. It's a pain and there has to be an easier way to do this.

 

[Andrzejek]

To be nice is important
But you've also learned the right way to explain your problem.

To get the BLACK portion of your outcome:

FIPS  County Name  [blue]Code1  Code2  Code3[/blue]   Counts
01003  Baldwin     [blue]AL001  AL003  AL061[/blue]     69
01097  Mobile      [blue]AL004  AL006  AL007[/blue]     71

should be easy with some SUM, GROUP BY and ORDER BY, but the [blue]BLUE[/blue] portion of your outcome is something else.

How do you know how many columns you'll need? Is it always [tt]Code1 Code2 Code3[/tt]? Can you have [tt]Code1 Code2 Code3 Code4 Code5 Code6[/tt], or just [tt]Code1 Code2[/tt]?

I have a code somewhere that can turn:[tt]
AL001
AL003
AL004
AL006[/tt]

into
[tt]
AL001, AL003, AL004, AL006[/tt]

but that may be not what you are after.

Have fun.

---- Andy

 

[runmd]

Andrzejek,

I'm very familiar with SUM, GROUP BY and ORDER BY and use them all the time but I think this is a bit more difficult. Well, I don't always know how many columns I will need. I'll need at least one for all counties and I think I have a few counties that will need at least 6 or more. Can you store your comma delimited list into one column? I think that will work for me but it may not be exactly what I was hoping for.

 

[Andrzejek]

Can you store your comma delimited list into one column?

Well, it is other way around: you have a column in your db with some values and you want to display the values in a comma delimited list, or space delimited list, or period delimited list, or...

If you have a table MyTable:[tt]
FieldToFlip ProjNo
AL001 Baldwin
AL002 Baldwin
AL003 Baldwin
AL004 Baldwin[/tt]

select substr(max(sys_connect_by_path(FieldToFlip,',')),2) AS X, ProjNo as pn
from (select ProjNo
     ,FieldToFlip
     ,row_number() over (partition by ProjNo
  order by FieldToFlip) rn
     from MyTable 
     WHERE SomeOtherField <> 'D'
                )
    start with rn = 1
    connect by prior rn = rn -1
    and prior ProjNo = ProjNo group by ProjNo

You get this as the outcome:
[tt]
X pn
AL001, AL002, AL003, AL004 Baldwin[/tt]

I will be the first to admit - I don't understand this sql and I don't know how it works, I just know it works.

Have fun.

---- Andy

 

[runmd]

Andrzejek,

Where does the WHERE clause fit in?

WHERE SomeOtherField <> 'D'

 

[Thargy]

run,

your requirement to store the various codes appear to be contrary to the way data is normally stored, i.e. normalised.

Can you state the business requirement here?

Regards

T

 

[Andrzejek]

You don't have to use the WHERE part of the query. It is a 'left over' from my code.

Have fun.

---- Andy

 

[runmd]

Well, I found a solution to my problem and it involved using SAS code. I have a lot of coworkers that are really good with SAS and it was easier to develop the code in that environment. It has a command called Merge that merges two or more tables together. I used a group by with the county code and the participant code during the merge and also added a counter. The counter was used to determine the number of times the county code occurred for every PHA. This way, I was able to quickly put together a table with all of the data I needed top perform the step of getting every PHA within every county in one table. I used another piece of SAS code to create the last table by county id, using arrays for participant code, number of units for each type of housing. This output table contained a unique county id, each PHA in it's own field, and number of units depending on what program it was.

This was really complicated to do in SQL since I haven't written a ton of it the past couple of years. I found SAS was just able to do a lot more in a few steps.