A query to do the following?

[Waynest]

I have a table which has following layout(simplified)
It holds cancelled order qtys per productfor the last 5 weeks max eg

SKU QTY WEEK

1234 1 1
1234 1 2
1234 1 4
1234 1 5
4321 2 3
Is it possible to devise a query which will return

SKU Wk1Qty Wk2Qty Wk3Qty Wk4Qty Wk5Qty

for every SKU in table?

 

[foxdev]

What you need, WayneSt, is yer basic cross-tab. If you have Excel handy, you might be able to use the PivotTable (R) feature to do this.

There is no simple, easy-to-use SQL statement that can do this for you, but for some examples, see the Books Online topic: cross-tab reports

 

[Waynest]

Thanks for pointing me in the right direction - the online help is quite good once you know what it is you want to know!

The solution wasnt that complex - this gave me what I needed

SELECT prdgrp, stkcat, grade, brand, style, shade, size,
SUM(CASE week WHEN 1 THEN totprs ELSE 0 END) AS Wk1,
SUM(CASE week WHEN 2 THEN totprs ELSE 0 END) AS Wk2,
SUM(CASE week WHEN 3 THEN totprs ELSE 0 END) AS Wk3,
SUM(CASE week WHEN 4 THEN totprs ELSE 0 END) AS Wk4,
SUM(CASE week WHEN 5 THEN totprs ELSE 0 END) AS Wk5
FROM CrossOffs
GROUP BY prdgrp, stkcat, grade, brand, style, shade, size

Thanks again

 

[foxdev]

That's a very elegant solution, Wayne - much better than what I could come up with (I would have written a stored procedure with some looping constructs).

Thank you!