![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
![]() |
Fri, Sep 23 2022 11:15 AM | Permanent Link |
R Casey | I've worked on this for a couple of days now, and I assume there is an easy solution that I'm missing.
I need a query that takes the following and returns the result below. There are 40 projects and they are constantly changing but a unit can only have two at a time. If I could number the projects by Unit when creating VIEW ( A would be 1, B would be 1, 2). I could use a Case stmt. Any suggestions would be greatly appreciated. VIEW: Unit Project Project_total A 121 $10.00 B 128 $5.00 B 123 $7.00 C 125 $15.00 RESULT: Unit Project_1 Project_1_total Project_2 Project_2_total A 121 $10.00 B 128 $5.00 123 $7.00 C 125 $15.00 |
Sat, Sep 24 2022 2:03 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | If you just want a report then I believe that FastReport has that capability. I think it was called crosstab. If you want to further manipulate the result then ElevateDB lacks PIVOT - you could have a look at this article from Tim
https://www.elevatesoft.com/articles?action=view&category=edb&article=cross_tab_result_sets_elevatedb_scripts which was referenced in a thread: Path: news.elevatesoft.com Lines: 26 Message-ID: <3BFFF572-88B7-4495-BC3C-3009A86DB703@news.elevatesoft.com> Date: Tue, 21 Jan 2014 16:30:11 +1100 From: "Adam H." <ahairsub5@removeme.jvxp.com> User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:24.0) Gecko/20100101 Thunderbird/24.2.0 MIME-Version: 1.0 Newsgroups: elevatesoft.public.elevatedb.general Subject: Crosstab / Decision Cube / PivotCube Result Set Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit which should get you started You could also visit Torry.net enter PIVOT into the search box and see if there's anything useful there. Roy |
Wed, Oct 19 2022 6:17 AM | Permanent Link |
Adam Brett Orixa Systems | It is theoretically possible to write SQL for the purpose you suggest.
Basically it involves a large number of sub-selects which are brought together into a single select. A Month-based example: SELECT SUM(Value) as Jan, 0 as Feb 0 as Mar, 0 as Apr, 0 as May, 0 as Jun, 0 as Jul, 0 as Aug, 0 as Sep, 0 as Oct, 0 as Nov, 0 as Dec FROM Sales WHERE EXTRACT(MONTH FROM DateSold) = 1 SELECT 0 as Jan, SUM(Value) as Feb 0 as Mar, 0 as Apr, 0 as May, 0 as Jun, 0 as Jul, 0 as Aug, 0 as Sep, 0 as Oct, 0 as Nov, 0 as Dec FROM Sales WHERE EXTRACT(MONTH FROM DateSold) = 2 You would write 12 SELECTS like this, and then merge them. You end up with a single row with each month's total ... I hope that is clear enough to explain the process. However with this you need 1 sub-select for every variant, and this often / usually is unmanagable. It is also possible to write this with an IF: SELECT IF(EXTRACT(MONTH FROM DateSold) = 1 THEN Sum(Value) ELSE 0) as Jan, IF(EXTRACT(MONTH FROM DateSold) = 2 THEN Sum(Value) ELSE 0) as Feb IF(EXTRACT(MONTH FROM DateSold) = 3 THEN Sum(Value) ELSE 0) as Mar, IF(EXTRACT(MONTH FROM DateSold) = 4 THEN Sum(Value) ELSE 0) as Apr, IF(EXTRACT(MONTH FROM DateSold) = 5 THEN Sum(Value) ELSE 0) as May, IF(EXTRACT(MONTH FROM DateSold) = 6 THEN Sum(Value) ELSE 0) as Jun, IF(EXTRACT(MONTH FROM DateSold) = 7 THEN Sum(Value) ELSE 0) as Jul, IF(EXTRACT(MONTH FROM DateSold) = 8 THEN Sum(Value) ELSE 0) as Aug, IF(EXTRACT(MONTH FROM DateSold) = 10 THEN Sum(Value) ELSE 0) as Sep, IF(EXTRACT(MONTH FROM DateSold) = 10 THEN Sum(Value) ELSE 0) as Oct, IF(EXTRACT(MONTH FROM DateSold) = 11 THEN Sum(Value) ELSE 0) as Nov, IF(EXTRACT(MONTH FROM DateSold) = 12 THEN Sum(Value) ELSE 0) as Dec FROM Sales But this variant also gets unmanageable pretty easily! Therefore I would strongly support using a 2 step process as suggested by the ever excellent Roy. * Write SQL to return a dataset. * Use an OLAP tool to crunch it into the "Cross tab" (or "Cube" or "Pivot") needed. Excel includes excellent built in capability to "Pivot" data based on datasets. Fast Reports is brilliant, and the "Fast" product offering includes a "Fast Cube" component which I use in all my applications, which offers the exact capability you are talking about. |
Wed, Oct 26 2022 3:14 PM | Permanent Link |
R Casey | Thanks to both Adam and Roy. I think I wrote something similar using a bunch of UNIONs and a MAX functions in the past. I just wanted to make sure there wasn't some trick I was missing; Roy seems to have a lot of them. I plan on using both of your suggestions..Roy's so I can understand Elevate better and Adam's because it's fast, easy. Thanks again! Bob
It is theoretically possible to write SQL for the purpose you suggest. Basically it involves a large number of sub-selects which are brought together into a single select. A Month-based example: SELECT SUM(Value) as Jan, 0 as Feb 0 as Mar, 0 as Apr, 0 as May, 0 as Jun, 0 as Jul, 0 as Aug, 0 as Sep, 0 as Oct, 0 as Nov, 0 as Dec FROM Sales WHERE EXTRACT(MONTH FROM DateSold) = 1 SELECT 0 as Jan, SUM(Value) as Feb 0 as Mar, 0 as Apr, 0 as May, 0 as Jun, 0 as Jul, 0 as Aug, 0 as Sep, 0 as Oct, 0 as Nov, 0 as Dec FROM Sales WHERE EXTRACT(MONTH FROM DateSold) = 2 You would write 12 SELECTS like this, and then merge them. You end up with a single row with each month's total ... I hope that is clear enough to explain the process. However with this you need 1 sub-select for every variant, and this often / usually is unmanagable. It is also possible to write this with an IF: SELECT IF(EXTRACT(MONTH FROM DateSold) = 1 THEN Sum(Value) ELSE 0) as Jan, IF(EXTRACT(MONTH FROM DateSold) = 2 THEN Sum(Value) ELSE 0) as Feb IF(EXTRACT(MONTH FROM DateSold) = 3 THEN Sum(Value) ELSE 0) as Mar, IF(EXTRACT(MONTH FROM DateSold) = 4 THEN Sum(Value) ELSE 0) as Apr, IF(EXTRACT(MONTH FROM DateSold) = 5 THEN Sum(Value) ELSE 0) as May, IF(EXTRACT(MONTH FROM DateSold) = 6 THEN Sum(Value) ELSE 0) as Jun, IF(EXTRACT(MONTH FROM DateSold) = 7 THEN Sum(Value) ELSE 0) as Jul, IF(EXTRACT(MONTH FROM DateSold) = 8 THEN Sum(Value) ELSE 0) as Aug, IF(EXTRACT(MONTH FROM DateSold) = 10 THEN Sum(Value) ELSE 0) as Sep, IF(EXTRACT(MONTH FROM DateSold) = 10 THEN Sum(Value) ELSE 0) as Oct, IF(EXTRACT(MONTH FROM DateSold) = 11 THEN Sum(Value) ELSE 0) as Nov, IF(EXTRACT(MONTH FROM DateSold) = 12 THEN Sum(Value) ELSE 0) as Dec FROM Sales But this variant also gets unmanageable pretty easily! Therefore I would strongly support using a 2 step process as suggested by the ever excellent Roy. * Write SQL to return a dataset. * Use an OLAP tool to crunch it into the "Cross tab" (or "Cube" or "Pivot") needed. Excel includes excellent built in capability to "Pivot" data based on datasets. Fast Reports is brilliant, and the "Fast" product offering includes a "Fast Cube" component which I use in all my applications, which offers the exact capability you are talking about. |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |