Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server - generating pivot table-like views

Posted on 2012-03-21
11
Medium Priority
?
395 Views
Last Modified: 2012-06-22
Example of tablesThe attached file shows a simple example of the problem I'm trying to resolve.
(My actual problem is one where the number of rows and columns is closer to hundreds or thousands of rows / columns, so I could do with a solution which is not just specific to this case)

I start with a table (Format A) where:
- the names of products (cars in the example) are the column titles;
- the names of attributes (colour, age, engine size etc) are the row titles;
- the values of those attributes are in the main body of the table and may be null.

To save this in SQL Server, I transform the data to a simple table of three columns (Format B):
- car
- attribute_name
- attribute_value
(all varchars)

The two problems I need to solve are:
(1) How can I display the SQL data back in the original format (Format A)? (both cars and attribute names can be in any order)
(2) I need to group the data according to common combinations of attributes.  e.g.
 select colour, engine, age FROM theTable group by colour, engine, age

BUT!!!! in both cases, the solution needs to be dynamic, so the views/SPs have to determine the list of attributes to group on (and list of cars to select from) at runtime.
My actual case will expand by number of attributes and number of procucts (cars) fairly regularly, so hard coding attribute names or car names into the SP or query is not feasible.

(PS - this topic shouldn't be listed under asp.net but for some reason in IE9, I can't deselect this!)
0
Comment
Question by:Beamson
[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
  • 5
  • 4
11 Comments
 
LVL 20

Assisted Solution

by:BuggyCoder
BuggyCoder earned 2000 total points
ID: 37746678
0
 
LVL 1

Author Comment

by:Beamson
ID: 37746705
Thanks for this.

I've given it a quick look and the dynamic version does look interesting.
However, my limited understanding of pivot tables is that they only do grouped/totalled (numeric) values in the body of the table.

I'll need to spend a little time giving it a go, but if its as simple as shown, I'll be over the moon!
0
 
LVL 1

Accepted Solution

by:
Beamson earned 0 total points
ID: 37747038
The solution is close enough for me.
I couldn't quite follow it but it pointed me in the right direction to start looking at the PIVOT function.

In the end, I found a simpler example, which, whilst it hard codes the possible values for the columns, is easier to grasp.

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

To go from there to doing it dynamically is just a case of generating the
"IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) " statement first, then executing it.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37747044
Glad you got the solution, please close this question!!!

Happy Programming
:-)
0
 
LVL 1

Author Comment

by:Beamson
ID: 37747098
I've requested that this question be closed as follows:

Accepted answer: 0 points for Beamson's comment #37747038
Assisted answer: 500 points for BuggyCoder's comment #37746678

for the following reason:

If you already have a pretty good understanding of FOR XML, PIVOT and temporary tables, the other ExpertsExchange thread given by BuggyCoder will probably point you in the right direction.<br /><br />However, if you want to understand the basics behind how it works and are struggling to piece things together, I think the blogs.msdn.com link gets the basics over pretty clearly, from which point, making this dynamic is a relatively small step.
0
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37747099
Have provided the solution that he was able to refer to and finally get his solution from. I think i deserve some points here....
0
 
LVL 1

Author Comment

by:Beamson
ID: 37747350
Er.  You've got all of them.
What is the problem?
0
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37747365
sorry i didn't see your comments before posting!!!
 
Frankly, I expected you to rate the answer and close, but you have left it on moderators to close it.
Anyways fine with me, the idea is that problem should be resolved and it has(I hope)...

Keep Well
Happy Programming :-)
0
 
LVL 1

Author Comment

by:Beamson
ID: 37747487
No problem!

Maybe I'm doing something wrong - I thought awarding points closes a post.
I rated your answer "A".

Then I received an e-mail from Experts Exchange to say you had reopened it and had requested a moderator.  Not sure what else I can do.  Guess we wait for a moderator to sort it out...
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

664 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