Hi John, Transposing / pivoting isn't so trivial in SQL. If you search on Stack Overflow, you'll find lots of questions related to that topic. Note that Oracle and SQL Server support PIVOT and UNPIVOT clauses, which might be along the lines of what you're trying to do.
Cheers Lukas 2013/6/21 John Childress <[email protected]> > thanks for the quick response... > > I guess I would be happy with any solution that would achieve the same > results as the sample query I posted. > > So MAX(CASE...END) is fine too... I just couldn't figure out how to do > it. > > Sorry... if it's easy and you have time to post how that would be great. > If not, this is just a one off for my system so I was going to just use a > prepared statement. > > I did read the decode part of the documentation, but confess I got lost... > > thanks again, > > John > > > On Friday, June 21, 2013 1:50:05 PM UTC-5, Lukas Eder wrote: > >> Hi John, >> >> I'm not sure if I understand your question. Is it about transposing in >> general? Or about writing a MAX(CASE ... END) expression? >> >> The CASE expression is indeed modeled by DSL.decode(), as you can see >> here: >> http://www.jooq.org/doc/3.0/**manual/sql-building/column-** >> expressions/case-expressions/<http://www.jooq.org/doc/3.0/manual/sql-building/column-expressions/case-expressions/> >> >> case (lower-cased) is a reserved word in Java, unfortunately, which is >> why I used DECODE() an Oracle function that has a similar purpose. >> >> Cheers >> Lukas >> >> >> 2013/6/21 John Childress <[email protected]> >> >>> I'm trying to transpose my data.... I was trying to figure out if >>> decode is what I need... I was trying to use a SelectQuery. >>> >>> My sql that "works" is here: >>> >>> SELECT >>> POINTID, >>> PERIODSTARTDATE, >>> PEAKTYPE, >>> max(CASE WHEN pricecomponent = 'A' THEN value END) AS DALMP, >>> max(CASE WHEN pricecomponent = 'B' THEN value END) AS DACONG, >>> max(CASE WHEN pricecomponent = 'C' THEN value END) AS DALOSS, >>> max(CASE WHEN pricecomponent = 'D' THEN value END) AS RTLMP, >>> max(CASE WHEN pricecomponent = 'E' THEN value END) AS RTCONG, >>> max(CASE WHEN pricecomponent = 'F' THEN value END) AS RTLOSS, >>> max(CASE WHEN pricecomponent = 'G' THEN value END) AS HALMP, >>> max(CASE WHEN pricecomponent = 'H' THEN value END) AS HACONG, >>> max(CASE WHEN pricecomponent = 'I' THEN value END) AS HALOSS, >>> max(CASE WHEN pricecomponent = 'J' THEN value END) AS MCP, >>> max(CASE WHEN pricecomponent = 'K' THEN value END) AS DART_RETURN, >>> COUNT >>> FROM PRICE_TABLE >>> WHERE (PERIODSTARTDATE >= DATE '2013-01-01' AND PERIODSTARTDATE <= DATE >>> '2013-05-17' AND POINTID = 12345 AND PEAKTYPE = 'OFFPEAK') >>> >>> Am I missing it in the docs? >>> >>> thanks, >>> >>> John >>> >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "jOOQ User Group" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to jooq-user+...@**googlegroups.com. >>> >>> For more options, visit >>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> >>> . >>> >>> >>> >> >> -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
