Converting data

Hi all,
I have the following questions :
1)
I have a query :
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
Which give me next output :
Pricelist class_01 bedr1
0000 Item1 0
0000 Item2 15
0000 Item15 10
All "CLASS_01" items go to 15 for each pricelist.
I like to convert this within SQL with Query or DTS to next structure,
so Pricelist and Bedr1 in 1 row with CLASS01 in columns (15 total) :
Pricelist item1 item2 item15
0000 0 15 10
2)
Other query is :
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
Output is :
debnr pricelist
1 2025
2 SALESPRICE
3 3001
What I need is to combine both queries to get 1 output as following,
so merge the 2 queries to 1 to get 1 combined output, with 1 special thing :
Where SALESPRICE = pricelist '0000' :
Deb Pricelist item1 item2 item15
1 2025 0 25 10
2 0000 0 10 10
1 3001 0 32 10
Can anybody help me ?
This is crosstab query
SELECT Pricelist ,MAX(CASE WHEN class_01 ='Item1' THEN bedr1 END)
'Item1,
..................... Do that for all items here
FROM Table
GROUP BY Pricelist
"Jeroen" <Jeroen@.discussions.microsoft.com> wrote in message
news:D64B9FBA-A315-4B5A-B088-2964054C3B3E@.microsoft.com...
> Hi all,
> I have the following questions :
> 1)
> I have a query :
> 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
> Which give me next output :
> Pricelist class_01 bedr1
> 0000 Item1 0
> 0000 Item2 15
> 0000 Item15 10
> All "CLASS_01" items go to 15 for each pricelist.
> I like to convert this within SQL with Query or DTS to next structure,
> so Pricelist and Bedr1 in 1 row with CLASS01 in columns (15 total) :
> Pricelist item1 item2 item15
> 0000 0 15 10
> 2)
> Other query is :
> 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
> Output is :
> debnr pricelist
> 1 2025
> 2 SALESPRICE
> 3 3001
> What I need is to combine both queries to get 1 output as following,
> so merge the 2 queries to 1 to get 1 combined output, with 1 special thing
> :
> Where SALESPRICE = pricelist '0000' :
> Deb Pricelist item1 item2 item15
> 1 2025 0 25 10
> 2 0000 0 10 10
> 1 3001 0 32 10
> Can anybody help me ?
>
|||Hi Uri,
not very into SQL (newbie).
I am not sure how to use this in my current query....
"Uri Dimant" wrote:

> This is crosstab query
> SELECT Pricelist ,MAX(CASE WHEN class_01 ='Item1' THEN bedr1 END)
> 'Item1,
> ..................... Do that for all items here
> FROM Table
> GROUP BY Pricelist
>
> "Jeroen" <Jeroen@.discussions.microsoft.com> wrote in message
> news:D64B9FBA-A315-4B5A-B088-2964054C3B3E@.microsoft.com...
>
>