September 17, 2007 at 9:16 am
I'm trying to use the Pivot to help replace an excel document that I have.
The data that I have is a breakdown of amounts by vendor / state for each day of the week. Example is:
Weekday / Vendor / State / Count / Amount
Saturday / 123 / OH / 50 / 100
Monday / 145 / GA / 75 / 25
What I'm wanting to do is be able to have each day of the week at the top and have the Vendor Name, State, Count and Amount as listed underneath each day of the week.
Is this something that can be done with Pivot or is there a better alternative to programatically doing all of the calculations.
September 17, 2007 at 10:26 am
You could use pivots to rotate the talbe, like:
SELECT Vendor,
[State],
[Monday],
[Teusday],
[Wednesday],
[Thursday],
[Friday],
[Saturday],
[Sunday]
FROM ( SELECT Vendor,
[State],
[Weekday],
Amount
FROM table1
) p PIVOT ( SUM(Amount) FOR [Weekday] IN ( [Monday], [Teusday],
[Wednesday], [Thursday],
[Friday], [Saturday],
[Sunday] ) ) AS pvt
But I'm not sure if this will do what you want. Could you describe what you mean by "listed underneath" (" week at the top and have the Vendor Name, State, Count and Amount as listed underneath each day of the week"). An example result would help. The result of the above query is :
Vendor State Monday Teusday Wednesday Thursday Friday Saturday Sunday
----------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
145 GA 25 NULL NULL NULL NULL NULL NULL
123 OH NULL NULL NULL NULL NULL 100 NULL
Regards,
Andras
September 17, 2007 at 10:34 am
I'm trying out your code, but what I want ultimately is:
| DateID | Data | ||||
| Monday | Tuesday | ||||
| Dept_No | State | Sum of Cnt | Sum of Amount | Sum of Cnt | Sum of Amount |
| ADSA | AL | ||||
| CA | 52 | $1,133.00 | 23 | $458.00 | |
| GA | 20 | $537.00 | 18 | $396.00 | |
| IN | 1 | $10.00 | |||
| KY | |||||
| MO | |||||
| NM | 4 | $75.00 | 2 | $35.00 | |
| NV | 1 | $22.00 | |||
| OH | 34 | $551.00 | 44 | $649.00 | |
| TX | 48 | $1,144.00 | 21 | $406.00 | |
| ADSA Total | 160 | $3,472.00 | 108 | $1,944.00 |
Repeated throughout for each of my vendors and days of the week (note this is copied from a pivot table in excel, so I cut it off after two days rather than send all of the cells).
September 17, 2007 at 10:37 am
Also, your code does everything except also give me the breakdown of the counts. I can always work to replace the nulls with 0's.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply