Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

How to transpose records results in a Query??

I am working with D6Ent, Paradox and the builtin QuickReport for the reports.
I got 2 tables, one with master records, an the seconds with details records. That is,

Table1            Table2
=====               =====            
A                   A1
                    A2
                    A3
B                   B1
                    B2
                    B3

I need to get this report.

A        A1           A2         A3
B        B1           B2         B3
====================================
SUM      A1+B1       A2+B2      A3+B3

Note: records on Table2 are fixed to 3 for each row in Table1, just for the example. Really they are dinamic, and you can set in a config table in which column of the report you list each value.

The only result that i get till now is a query which result is something like this:
A A1
A A2
A A3
B B1
B B2
B B3

So i think that if i can transpose each grouped record i can get something like I need (????)
Thanks you all...
0
spk2000ar
Asked:
spk2000ar
  • 6
  • 3
  • 3
  • +2
1 Solution
 
YodaMageCommented:
are you looking for something like:

select t1.field1, t2.field1, t2.field2, t2.field3
 from table1 t1, table2 t2
 where (t1.field1 = t2.{condition for relation})

*OR*

SELECT table1.field1, table2.field1, table2.field2,
  table2.field3
FROM table2
   INNER JOIN table1 ON (table2.field1{or condition for
   relation = table1.field1)
0
 
spk2000arAuthor Commented:
No, in your example field1..field3 are in the same record... but there are 3 RECORDs in table 2 that match the conditions in my example, i.e., A1, A2, and A3 are records on table2, not fields...
0
 
YodaMageCommented:
then

SELECT table1.field1, table2.field1
FROM table1
  INNER JOIN table2 ON (table1.field1 = table2.field1)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ITugayCommented:
Hi spk2000ar,

you are looking for technique named "CROSSTAB". You have to build crosstab query. It mean that every column in report is row from the table.
In this case, you have report with various numer of columns. QR doesn't allow to clone columns on demand. So, you have to build report dynamically. At first, you have to prepare your data in memory and then show it in QR. Easy way would be to use TQRMemo. In this case you do not need create QRLables in runtime.

--------
Igor.
0
 
DragonSlayerCommented:
Umm... perhaps the Decision Cube component might help? :)
0
 
spk2000arAuthor Commented:
YodaMage,
your second answer let me get the example i wrote on my initial question:
A A1
A A2
A A3
B B1
B B2
B B3
and you can group it, but then the QReport i get is something like this

GroupBand     A
DetailBand        A1
DetailBand        A2
DetailBand        A3
GroupBand     B
DetailBand        B1
DetailBand        B2
DetailBand        B3

and that not what i need... that is something like this
A        A1           A2         A3
B        B1           B2         B3
====================================
SUMMARY  A1+B1     A2+B2     A3+B3

Itugay,
perhaps thats the solution, but i don't get the idea. A quick example of a crosstab query may help.Think on fixed columns in the report, they are not dinamically created.

DragonSlayer,
i think on that, but i don´t understand the correct functionality of the DecisionCube. Can you explain it more???

thanks to you all!!!!!!!!!

0
 
spk2000arAuthor Commented:
Listening...
0
 
DragonSlayerCommented:
Maybe you can check this out

http://www.delphizine.com/features/1997/11/di199711cj_f/di199711cj_f.asp

You have to register to log in, but I think you can try to sign up for their 1-month free trial.
0
 
ITugayCommented:


>>Think on fixed columns in the report, they are not dinamically created.

So it should be easy. Can you prepare result in matrix? If so, then place few TQRLabels on report and use OnPrint event to show prepared in matrix data. Let me know if you still need sample.

-------
Igor.
0
 
spk2000arAuthor Commented:
ITugay, i think in that matrix as a temporal table, which records are appended programmatically by the use of subsequent querys, thats because the number of record to be listed in the columns are not fixed, and can happened something like this:
A        A1           A2+A3
B        B1            B2          B3
C        C1            C2          
====================================
SUMMARY  A1+B1+C1    A2+A3+B2+C2   B3

But i get a great overhead on generating the data i needed.
If can be other solution, something more direct, it would be better...
0
 
YodaMageCommented:
I don't use Quick Reports, so tough for me to say, but you need a cross tab report as ITugay said. He stated that there is no cros tab functionality to QR, so you should essentially use a memo field, add your data and arrange as needed using straight text manipulation, until you have the layout you want in the memo, then run your memo out to QR.

I have one similar report in an application, where I use a temp table which is created dynamically with needed columns, values are then calculated and populated so that I essentially have data in format required, then report (RB in my case), is printed, with pplabel.captions assigned at runtime. (And visible property adjusted as needed to remove access captions). The downside is you do have to have a 'Maximum' number of columns with this technique. You could go fully dynamic if you wanted to create and place labels dynamically.
0
 
spk2000arAuthor Commented:
I think thats all, the best way instead of the memo was the initial temp table.
Do you know if i can accept two comments as answer... or hoy can i slipt the points...
Because i believe that Itugay made gives the link, and YodaMage make the most effort.
Please, how can i do that???????
0
 
ITugayCommented:
hi,

you can ask in "community support" topic area to split points. Do not forget to provide link to this Q.

------
Igor.
0
 
Computer101Commented:
Points reduced for split.  Now you can accept an experts comment as an answer.  After that, make another question for the other expert in this topic area.

Computer101
E-E Moderator
0
 
spk2000arAuthor Commented:
ITugay, please comment the follow question to take your points!!

http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=delphi&qid=20304983

Thanks!!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now