Crosstab pivot problem with multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Crosstab pivot problem with multiple tables

Author

Topic

MrOnion
Starting Member

1 Post

Posted - 2012-02-07 : 10:11:50

Hi all,

I'm trying to convert a access crosstab query to sql server and it's causing me trouble. I have done couple of simple crosstab conversions with one table succesfully but this is a larger query.

I use access as a front end and tried to leave the crosstab query as a local one and all other queries used by the local query as views in sql server. When ran it never finishes or at least I never had to nerves to wait that long ;). SQL trace log shows a lot of sp-executes during the run so access is probably not using the queries the right way...

Anyway, the idea is that I have 4 views (is it ok to use views instead of tables?) that are joined. Then I have one view that is used for the pivot information. It has two columns a value and a title. The title is used as the columns after the other select columns. The value is used as the pivot value.

Here is the original access query to open this up. The idea is to collect a report per car and driver to see what costs they have in each cost group.

---
TRANSFORM Sum(Net) AS SumOfNet

SELECT
Cars.LicenceNumber,
Cars.Driver,
Costs.Customer AS CustomerNumber,
Customer.CustomerName,
Customer.PostAddress,
Customer.PostNumber,
Customer.PostOffice

FROM
Costs
LEFT JOIN Cars ON Costs.Vehicle = Cars.ID
LEFT JOIN CostGroups ON Costs.CostGroup = CostGroups.CostGroup
LEFT JOIN Customer ON Costs.Customer = Customer.ID

WHERE
Costs.PeriodStart <= #01/01/2012#

GROUP BY
Cars.LicenceNumber,
Cars.Driver,
Costs.Customer AS CustomerNumber,
Customer.CustomerName,
Customer.PostAddress,
Customer.PostNumber,
Customer.PostOffice

PIVOT CostGroups.Title;
---

So, I join all the tables using left join and give a where condition. Then I group the rows for the aggregate function. The crosstab thing is to add columns from the CostGroups table that looks like this:

| CostGroup | Title |
| 100 | Group1 |
| 200 | Group2 |
| 300 | Group3 |
|..etc..|..etc..|

So, the result would be the car and customer info and after that the sum of net values for every group per car and person.

Is this possible the make using SQL Servers pivot? Thank you for any help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-03-22 : 04:56:46

Have you tried this?
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources