December 4, 2008 10:54pm pst

1. DanielWilson 37,682
2. angelIII 11,002
3. mwvisa1 7,800
4. Ha11y 7,200
5. it-dept 6,100
6. chapmandew 5,672
7. BrandonG... 4,100
8. dbaduck 4,000
8. Binuth 4,000
10. matrixnz 3,200
11. frankytee 3,000
11. dqmq 3,000
11. Joe_Woo... 3,000
14. BillBach 2,750
15. grant300 2,500
15. b0lsc0tt 2,500
15. TechSoEasy 2,500
15. jimpen 2,500
19. tedbilly 2,250
20. leew 2,200
21. ScottPletc... 2,064
22. mlmcc 2,000
22. EugeneZ 2,000
22. matty1stop 2,000
22. devshb 2,000
22. ee_rlee 2,000
22. tntmax 2,000
22. wmeerza 2,000
22. Mr-Madc... 2,000
22. KICUSek 2,000
22. hielo 2,000
22. routinet 2,000
22. ushastry 2,000
22. drequinox 2,000
22. Yiogi 2,000
22. dchan17 2,000
22. IdoMillet 2,000
22. jjardine 2,000
22. ragi0017 2,000
22. JezWalters 2,000
22. Rrooter 2,000
22. graye 2,000

1. DanielWilson 56,557
2. angelIII 26,102
3. it-dept 8,100
4. mwvisa1 7,800
5. Ha11y 7,200
6. dqmq 6,000
7. chapmandew 5,672
8. TechSoEasy 4,500
8. jimpen 4,500
10. BrandonG... 4,100
11. appari 4,000
11. sujith80 4,000
11. Binuth 4,000
11. dbaduck 4,000
15. grant300 3,250
16. matrixnz 3,200
17. frankytee 3,000
17. acperkins 3,000
17. routinet 3,000
17. Joe_Woo... 3,000
17. xDJR1875 3,000
17. samtran0... 3,000
23. mlmcc 2,750
23. BillBach 2,750
25. ptjcb 2,500
25. b0lsc0tt 2,500

10.12.2008 at 01:57AM PDT, ID: 23807469

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

9.1

How can I rotate cells with data to columns in a single query?

Asked by TinoNL in SQLBase, MS SQL Server

Tags: SQL / VB.NET

I want to turn/rotate certain information that are displayed in joined rows, so they will become extra columns in the query output.

To illustrate this problem, I have set up an example in which I make use of a small database that contains 3 tables: Products, ProductGroup and Projects. (see the image).

Each time a project is done, different products are used or sold. These are registered in the Product table and are linked to the Project, with the ProjectID. The ProductGroupID in the Product table indicates what kind of group this Product belongs to.

To each project, products are linked that represent costs that are of a certain type and therefore belong to a product group.

I would like to generate an overview of the Projects, where the product groups are dynamically added as columns, to specify the different costs per project. On the image you can see an example of the desired output.

However, there are some additional functionality requirements:
1. The product groups that are be displayed as additional columns in the result, should be selected dynamically, by an parameterized query. In this example there are 2 cost groups; in reality there are hundred different product groups that need to be displayed in various ways.
2. Not all the product groups are filled with costs all the time. Hence the absence of travel costs for the project Cloud puffing in this example.

My ideas:
I have thought of several solutions; but they are either too memory consuming, esp. with large selections, or werent meeting up to one the above condition:
1. Use union statements to add extra columns to the result. In this experiment, it proved to become a cluttered SQL string, that wasnt exactly nicely scalable.
2. I have loaded all Products into an array in ASP.NET first and then iterated through a list of Projects, each time testing if the Product perhaps belonged to both that Project as well as a selected product group. This works okay for small lists, but is quite heavy on the processing and needs a lot of programmatic code.

It would be great to get some hints for directions to take in this puzzle.Start Free Trial

Attachments:

xx.jpg (102 KB) (File Type Details)

example

Keywords: How can I rotate cells with data to colu…

	Title
1	Rotate Text in MS Word
2	Ad Rotator
3	BACKUP ROTATION CYCLE ON HDD
4	Rotate Screen
5	Rotation
6	rotation animation in powerpoint

Loading Advertisement...

20080716-EE-VQP-32 / EE_QW_2_20070628

How can I rotate cells with data to columns in a single query?

Asked by TinoNL in SQLBase, MS SQL Server

Tags: SQL / VB.NET

About this solution