Pivot for weeks
-
I have the following code:
SELECT
dbo.fn_Get_Week_By_Date(DDATE) AS Week,
PRODUCT_CODE_CREW_MEMBER_SHIFT AS Product
FROM
[pepperbase].pepperbase.TRANSACTION_ADDRESS
WHERE
DDATE BETWEEN
DATEADD(qq,DATEDIFF(qq, 0, '2010-07-01'),0)
AND
DATEADD(qq,DATEDIFF(qq,-1, '2010-07-01'),-1)
AND
PROJECT_CODE_CREW_PROJECT_OFFI LIKE 'AZG%'
AND
YN_CANCEL = 'No'
It displays al the products with the week. It displays the following:
Week Product
29D6
30D5
29D7
31D10
28D6
28D9
What I want is to rotate the results that I have the weeks on top and the amount of product onder it like this:
Product 28 29 30 31
D5 77 10 11 15
D6 55 67 89 15
D7 24 56 66 51
When I try the following code I get the error:
Msg 207, Level 16, State 1, Line 12
Invalid column name 'Product'.
Code:
SELECT
Product,
[26], [27], [28], [29], [30], [31]
FROM(
SELECT
dbo.fn_Get_Week_By_Date(DDATE) AS Week,
PRODUCT_CODE_CREW_MEMBER_SHIFT AS Product
FROM
[pepperbase].pepperbase.TRANSACTION_ADDRESS
WHERE
DDATE BETWEEN
DATEADD(qq,DATEDIFF(qq, 0, '2010-07-01'),0)
AND
DATEADD(qq,DATEDIFF(qq,-1, '2010-07-01'),-1)
AND
PROJECT_CODE_CREW_PROJECT_OFFI LIKE 'AZG%'
AND
YN_CANCEL = 'No'
) X
PIVOT
(
COUNT(Product)
FOR Week
IN ([26], [27], [28], [29], [30], [31])
) PVT
What do I have to change to get the result I want.
Thanx in advance
-
Seriuosly I've been looking at that code for several hours.
Thanx!
-
That's one of the reasons I prefer using the "old-fashioned" cross tab method over PIVOT.
One of the other reasons is to make it dynamic more easily.
-
How could I make this dynamic because that's what I'm trying to do now but I keep getting stuck
-
See the CrossTab link in my signature and change your query accordingly.
Then read the DynamicCrossTab article (also refernced in my signature) to make it dynamic.
Give it a try and see if you can get it to work. If not, post table def and sample data in a ready to use format as described in the first link in my signature together with what you've tried so far and there'll be quite a few people helping you to get the issue resolved.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply