Transpose QUestion ...

[request]

I have the stored procedure output as follows:

name reghrs prodhrs vachrs
TA 2 0 0
TA 0 6 0
DA 8 0 0
DA 0 2 0
DA 0 0 3

Instead, I want my stored procedure output to look as follows:

name reghrs prodhrs vachrs
TA 2 6 0
DA 8 2 3

HOW CAN I DO IT??

Please help. Thanks.

 

[SantaMufasa]

Request, I can't tell for sure without seeing your current code that produces your current output, but it seems to me you want something like:

SELECT name, sum(reghrs), sum(prodhrs), sum(vachrs)
from <tablename>
group by name;

¿Sí/No?

 

[request]

SantaMufasa:

Thanx for the response.
I cannot use the query that you have suggested. The reason is that my table tt has the following fields:
name
hrs
code

For each category (RegHrs / ProdHrs / VacHrs), there is a code defined.

So to calculate each category, I am doing as follows:
select name,
sum(hrs) reghrs
from tt
where code in ('AA','BB')
UNION
select name,
sum(hrs) prodhrs
from tt
where code in ('CC','DD')
UNION
select name,
sum(hrs) vachrs
from tt
where code in ('EE','FF')

So, with the above query it would produce the output as follows:


name reghrs prodhrs vachrs
TA 2 0 0
TA 0 6 0
DA 8 0 0
DA 0 2 0
DA 0 0 3

However, I want my output to look like as follows:

name reghrs prodhrs vachrs
TA 2 6 0
DA 8 2 3


Please let me know if you know how this can be done.
Thanks in advance.

 

[SantaMufasa]

Request,

There are a couple of fixes we must make for your code to work:

1) When you have a SELECT that includes even one expression that is a GROUP expression [such as SUM(), MIN(), MAX(), AVG(), COUNT()], then ALL expressions in the SELECT must by GROUP expressions. IF the other expressions are not arguments of a group function (see list above), then you cause them to become group expression by listing them in a GROUP BY clause. So, your current SELECT(s), since they contain the expressions, SUM(hrs), the expression NAME must appear in a GROUP BY clause, else you receive a first-pass interpreter error.

2) Each of your SELECTs contain two expressions. Therefore, your output will be only two columns wide. To display 4-column output, each of your SELECTs must contain 4 expressions.

3) If you want report totals, you can use SQL*Plus features

To illustrate the implementation of all this, I'll show the contents of your table &quot;TT&quot;, followed by the report script:

Table TT contents:

Select * from tt;

NAME HRS CODE
---- ---- ----
TA 1 AA
TA 1 BB
TA 1 CC
TA 1 CC
TA 1 CC
TA 1 DD
TA 1 DD
TA 1 DD
DA 4 AA
DA 4 BB
DA 1 CC
DA 1 DD
DA 1 EE
DA 1 EE
DA 1 FF

Script:

break on report
compute sum of reghrs prodhrs vachrs on report
select name, sum(hrs) reghrs,0 prodhrs, 0 vachrs
from tt
where code in ('AA','BB')
group by name
UNION
select name, 0, sum(hrs), 0
from tt
where code in ('CC','DD')
group by name
union
select name, 0, 0, sum(hrs)
from tt
where code in ('EE','FF')
group by name
order by name desc
/

NAME REGHRS PRODHRS VACHR
---- ---------- ---------- ---------
TA 0 6 0
TA 2 0 0
DA 0 0 3
DA 0 2 0
DA 8 0 0
---------- ---------- ---------
sum 10 8 3

Let me know your thoughts,

Dave

 

[request]

SantaMufasa:

I am sorry I did not completely state my query. However, in my query in the stored procedure, I do have group by and the same number of columns in select clause.

However, I do not want my output to look as follows;
NAME REGHRS PRODHRS VACHR
---- ---------- ---------- ---------
TA 0 6 0
TA 2 0 0
DA 0 0 3
DA 0 2 0
DA 8 0 0
---------- ---------- ---------
sum 10 8 3

I want it as follows:

NAME REGHRS PRODHRS VACHR
---- ---------- ---------- ---------
TA 2 6 0
DA 8 2 3

 

[SantaMufasa]

Request,

Here, then, is the new code version to do what you want:

break on report
compute sum of reghrs prodhrs vachrs on report
select a.name
, nvl(reghrs,0)reghrs
, nvl(prodhrs,0)prodhrs
, nvl(vachrs,0)vachrs
from
(select name, sum(hrs) reghrs
from tt
where code in ('AA','BB')
group by name) a,
(select name, sum(hrs) prodhrs
from tt
where code in ('CC','DD')
group by name) b,
(select name, sum(hrs) vachrs
from tt
where code in ('EE','FF')
group by name) c
where a.name = b.name (+)
and b.name = c.name (+)
order by name desc

NAME REGHRS PRODHRS VACHRS
---------- ---------- ---------- ----------
TA 2 6 0
DA 8 2 3
---------- ---------- ----------
sum 10 8 3