Hello,

does anybody have a sollution for the next problem :

1) I have 1 query which gives me next out put :
No. list
1 2025
2 3000

2) Other query gives me next out put :
List item descr.
2025 10 descr1
2025 30 descr2
2025 25 descr3
3000 15 descr2

Problem is to merge these 2 queries to 1 output :
----
No. List item1 item2 item3 descr1 descr2 descr3
1 2025 10 30 25 descr1 descr2 descr3
2 3000 15 descr2
---

How can I merge 2 queries to one to do this.
At this moment I get out 2 separate XLS files using DTS.
If I can work from there it is OK for me,
but every "Item and descr" has to be fixed to a collum in XLS


Here my 2 queries which give me the output...

1)
-----
SELECT cicmpy.debnr, cicmpy.PriceList FROM cicmpy LEFT OUTER JOIN cicntp ON
cicmpy.cnt_id = cicntp.cnt_id WHERE debcode IS NOT NULL

ORDER BY cicmpy.debcode
--------

2)
----------
SELECT staffl.prijslijst AS pricelist, staffl.ARTCODE AS PhantomItem,
(CASE items.class_01 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode =
items.class_01 AND itemclasses.CLASSid = 1) END) AS class_01,
staffl.bedr1,
(CASE items.class_10 WHEN 'ALL' THEN 'ALL' ELSE (SELECT
itemclasses.description FROM itemclasses WHERE itemclasses.itemClassCode =
items.class_10 AND itemclasses.CLASSid = 10) END)

AS class_10 FROM staffl INNER JOIN stfoms ON stfoms.prijslijst =
staffl.prijslijst INNER JOIN items ON items.itemcode = staffl.artcode
AND items.type = 'P' AND stfoms.type = 'S' AND stfoms.main_pricelist <> 1
AND SUBSTRING(staffl.ARTCODE, 1, LEN(staffl.prijslijst)) = staffl.prijslijst
AND items.IsSalesItem = 0
ORDER BY staffl.pricelist
---------


I like to do it with use of DTS because of a scheduling
Thanks for any help

Jeroen

Hello Jeroen,


So you want to go from many rows + few columns to Many columns and few rows

Can you do this in a single source query?

It is going to be painful to do this in DTS.


Allan