Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2008-10-12
6
Medium Priority
?
427 Views
Last Modified: 2013-11-16
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.
xx.jpg
0
Comment
Question by:TinoNL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22697852
What version of SQL Server are you using?  The zone suggests SQL 2000, but just want to confirm.
0
 

Author Comment

by:TinoNL
ID: 22698773
Oh, ik work on both MS SQL2000 as MS SQL2005 platforms. If 2005 with .NET would allow certain technique (some xml output or so?) that would enable a certain solution, then it's no bother migrating for good to 2005.  
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22699121
SQL 2005 has PIVOT keyword that works pretty well other than the fact you must hardcode the column names; however, you can mitigate this by using Dynamic SQL statement or creating SQL Statement in your .NET code based on the values from the table you want to pivot as columns.

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:TinoNL
ID: 22700930
Thank you for pointing me to this article; it seems like a nice push in the right direction. Downside is hardcoding the column names;
After some time of SQL programming, dynamic SQL always seems to be the easy way out to me. At least; in the end I always came down to static, parameterized equivalents, that proved faster and more readable.  
For generating SQL from .NET; I preferrably use Stored Procedures; a lot of data filtering already needs to be done, before I can start pivotting; this brings me back to the previous arguments.
I have to dive into the thing, but I'm concidering combining this pivot statement with some sort of temporary table that have values for the columns.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 22701092
You could always have your stored procedure update a temp/working table or view and then you return distinct values of the column data to be pivoted to VB then use that to construct query to PIVOT view.

A lot of steps, but allows you to use stored procedure for bulk of T-SQL and then use VB.NET for automation of the hardcoding of columns for representation of data.

Alternative is to pass the data through Access.  You can use the crosstab wizard of manually code with TRANSFORM and PIVOT statements -- don't believe you have to hardcode column names, but maybe I am wrong -- know I didn't have to with crosstab wizard.
0
 

Author Closing Comment

by:TinoNL
ID: 31505418
Cool; I have quite the homework to dive into;) Thanks a lot!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question