Hello Xypron, Unpivot/Uncrosstab in PostgreSQL - Hack https://cgsrv1.arrc.csiro.au/blog/2010/05/14/unpivotuncrosstab-in-postgresql/
Function for UNPIVOT http://www.texastoo.com/post/2008/12/02/TSQL-UNPIVOT.aspx I think it would be good to have some help functions in Tables e.g. Transpose/Pivot and Untranspose/Unpivot. I think this is very useful since all the time we get results in glpk/mathprog in untranspose form and we have to transpose the results so we can use graphing software e.g. gnuplot, R to visualise the result. Thanks. Noli On 9/17/10, Noli Sicad <[email protected]> wrote: > Hello Xypron, > > Thank you very much the workaround using alias names for the columns. > It works great. > > I also patched glpk-4.44 (Ubunut 10.04). I just copy your src folder > and then configure, make clean, make and then make install. > > It is working now with multilines SQL queries. > > I have another problem - Unpivot / uncrosstab table as input. This is > opposite to have I have asked you before - transposing data / pivot. > > Sqlite does support not have unpivot / uncrosstab and crosstab/transpose. > > I am working on AMPL model which I am trying to translate to MathProg > using Sqlite. It would useful for farmers and foresters for forest > management and carbon sequestration modelling. > > Here is the problem. > > The AMPL model reads data using this script: > > ~~~~~~~ > printf "Reading Yields....\n"; > > table qryData IN "ODBC" "Otago.mdb" : [i ~ Croptype, j ~ Age], {p in > PRODUCT: Type[p] <> 'Residue'} <Yield[p,i,AgeToAgeClass[j]] ~ (p)>; > > read table qryData; > > ~~~~~~~~ > > It seems that AMPL table has algorithm to unpivot / de-normalised data > > (This is unpivot concept in Oracle 11g > http://www.sqlsnippets.com/en/topic-12698.html > > I think MySQL and MS SQL support these features ) > > Here are the tables. > > > CREATE TABLE tblData > ( > Croptype varchar (32), > Age int, > Area float, > TRV int, > SCOST money, > PCOST money, > P1P2 float, > SLOG float, > PULP float, > CFREV money, > LCOST money, > TCOST money, > SACOST money, > RCOST money > > ) > > > > tblProduct > > ProductID Product Type Status ProductName > 14 CFREV Final Revenue Clearfell Revenue > 21 LCOST Final Cost Harvesting Cost > 3 P1P2 Final Yield Pruned > 2 PCOST Intermediate Cost Pruning Costs > 13 PULP Final Yield Pulp > 24 RCOST Final Cost Roading Cost > 23 SACOST Final Cost Cost > 1 SCOST Intermediate Cost Silvilcutural Costs > 4 SLOG Final Yield Sawlog > 22 TCOST Final Cost Transport Cost > 0 TRV Final Yield Total Recoverable Volume > > > tblData > > Croptype Age Area TRV SCOST PCOST P1P2 SLOG PULP > CFREV LCOST TCOST SACOST RCOST > OTD 1 0 12 842 0 0 7 5 482 > 187 0 20 75 > OTD 2 111.2 25 0 0 0 15 10 1005 > 373 0 40 149 > OTD 3 483.5 39 0 0 0 23 16 1568 > 577 0 63 156 > OTD 4 467.4 53 0 0 0 32 21 2131 > 784 0 86 163 > OTD 5 407.5 67 0 0 0 40 27 2693 > 987 0 108 169 > OTD 6 177.2 81 0 0 0 49 32 3256 > 1194 0 131 176 > OTD 7 163 95 0 0 0 57 38 3819 > 1401 0 154 183 > OTD 8 574 109 0 0 0 65 44 4382 > 1604 0 177 189 > OTD 9 384.1 123 0 0 0 74 49 4945 > 1811 0 200 196 > > > The wanted outcome, Yield table > > Croptype Age Product Yield > OTD 1 TRV 12 > OTD 2 TRV 25 > OTD 3 TRV 39 > OTD 4 TRV 53 > OTD 5 TRV 67 > OTD 6 TRV 81 > OTD 7 TRV 95 > OTD 8 TRV 109 > OTD 9 TRV 123 > OTD 10 TRV 137 > OTD 1 SCOST 842 > OTD 2 SCOST 0 > OOTD 3 SCOST 0 > OTD 4 SCOST 0 > OOTD 5 SCOST 0 > .. > > Now, how do we implement this in MatProg, I don't mind if we write the > tables in csv text file first then write the csv file to create the > tables. > > > Again, here's AMPL table implementation using MS access db. > > > > printf "Reading Yields....\n"; > > table tblData IN "ODBC" "Otago.mdb" : [i ~ Croptype, j ~ Age], {p in > PRODUCT: Type[p] <> 'Residue'} <Yield[p,i,AgeToAgeClass[j]] ~ (p)>; > > read table tblData; > > > > Now, how do we do this part? > > > set S3, dimen 3; > > table tab IN "ODBC" 'Driver=SQLITE3;Database=Otago_p.sqlite;' > > > > > S3 <- [Product,Croptype, Age], Yield ~ Yield; > > Thanks again. > > Regards. > > Noli > _______________________________________________ Help-glpk mailing list [email protected] http://lists.gnu.org/mailman/listinfo/help-glpk
