?
Solved

How to transpose records results in a Query??

Posted on 2002-05-22
15
Medium Priority
?
210 Views
Last Modified: 2011-09-20
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
Comment
Question by:spk2000ar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +2
15 Comments
 
LVL 4

Expert Comment

by:YodaMage
ID: 7027894
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
 
LVL 4

Author Comment

by:spk2000ar
ID: 7028129
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
 
LVL 4

Expert Comment

by:YodaMage
ID: 7028145
then

SELECT table1.field1, table2.field1
FROM table1
  INNER JOIN table2 ON (table1.field1 = table2.field1)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:ITugay
ID: 7028913
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
 
LVL 14

Expert Comment

by:DragonSlayer
ID: 7028944
Umm... perhaps the Decision Cube component might help? :)
0
 
LVL 4

Author Comment

by:spk2000ar
ID: 7029869
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
 
LVL 4

Author Comment

by:spk2000ar
ID: 7030961
Listening...
0
 
LVL 14

Expert Comment

by:DragonSlayer
ID: 7031085
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
 
LVL 9

Expert Comment

by:ITugay
ID: 7031358


>>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
 
LVL 4

Author Comment

by:spk2000ar
ID: 7031726
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
 
LVL 4

Accepted Solution

by:
YodaMage earned 480 total points
ID: 7031897
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
 
LVL 4

Author Comment

by:spk2000ar
ID: 7032460
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
 
LVL 9

Expert Comment

by:ITugay
ID: 7034198
hi,

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

------
Igor.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 7035017
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
 
LVL 4

Author Comment

by:spk2000ar
ID: 7036806
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month12 days, 18 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question