Thread: do I need a table function to do this
I have been given an interesting problem to solve in a postgres db. I was given two tables t1: site treatment variable id (pk) ------------------------------------------------------------- A X BLUE 1A B Y RED 2B A Y GREEN 3A t2: rowid (pk) timestamp BLUE RED GREEN ----------------------------------------------------------------------------------------- 1 1332493200 3.4 2.1 5.8 2 1332496800 3.2 2.0 5.8 3 1332500400 3.3 2.2 6.0 I need to combine the site and treatment information from t1 with the variable records in t2. I think I will have to combinethese one variable at a time. Something like this (I'm not using the word join, because I don't think this is a joinin the regular sense. It's more like some kind of crazy pivot table thing!): t3: rowid (pk) timestamp BLUE site treatment ------------------------------------------------------------------------------------- 1 1332493200 3.4 A X 2 1332496800 3.2 A X 3 1332500400 3.3 A X and then: t4 rowid (pk) timestamp RED site treatment ------------------------------------------------------------------------------------- 1 1332493200 2.1 B Y 2 1332496800 2.0 B Y 3 1332500400 2.2 B Y Is this even possible?
Hello Kirk,
Is this even possible?
It looks like if you could "un-pivot" your t2, then you could do a join between that result and t1 and have your desired table. Others may be able to suggest a more direct solution, but I've successfully adapted the one given here, with my own wide-format tables:
Good luck,
Bryan
<meta content="text/html; charset=ISO-8859-1" http-equiv="Content-Type"> body p { margin-bottom: 0cm; margin-top: 0pt; } <body style="direction: ltr;" bidimailui-detected-decoding-type="latin-charset" bgcolor="#FFFFFF" text="#000000"> Do you have a unique key on site,variable? If not, what do you want in the treatment column if there are rows for both treatments X and Y or 2 Xs for a specific site and variable? If your data makes sense, you can pivot table t1 and then full join t2. Sim On 12/29/2012 11:45 PM, Kirk Wythers wrote: <blockquote cite="mid:99CCEF6F-37D8-4C1A-B4A7-DA8FF19D5048@umn.edu" type="cite"> I have been given an interesting problem to solve in a postgres db. I was given two tables t1: site treatment variable id (pk) ------------------------------------------------------------- A X BLUE 1A B Y RED 2B A Y GREEN 3A t2: rowid (pk) timestamp BLUE RED GREEN ----------------------------------------------------------------------------------------- 1 1332493200 3.4 2.1 5.8 2 1332496800 3.2 2.0 5.8 3 1332500400 3.3 2.2 6.0 I need to combine the site and treatment information from t1 with the variable records in t2. I think I will have to combinethese one variable at a time. Something like this (I'm not using the word join, because I don't think this is a joinin the regular sense. It's more like some kind of crazy pivot table thing!): t3: rowid (pk) timestamp BLUE site treatment ------------------------------------------------------------------------------------- 1 1332493200 3.4 A X 2 1332496800 3.2 A X 3 1332500400 3.3 A X and then: t4 rowid (pk) timestamp RED site treatment ------------------------------------------------------------------------------------- 1 1332493200 2.1 B Y 2 1332496800 2.0 B Y 3 1332500400 2.2 B Y Is this even possible?