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     

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

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.

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.