Solved

sql data formatting scenario

Posted on 2014-10-21
3
68 Views
Last Modified: 2014-12-03
hi experts
when i query my table
select Product, Name, Value, Yearly_Amount from Customer_Research
The data i get is something like this
Data from my table
So if you see there are three products MBS,OOK,ZRR. The Name,Value,Yearly_Amount are same for all these products which is
TOM Adjust      60      2772
MAC Inc             20      10406438
SDRF             58      2764
PPLED             89      1

What i am trying to do is to get a result set like this ...    
trying to show / group by multiple column values
So basically trying to group by Name/Value/Yearly_Amount entries and see the products
But that might not be the best way to represent the data, maybe a little confusing to the user perhaps?

This representation woud be best i think
comma seperated grouping
Is it possible to do what i am trying to do?
Any sugessions would be greatly appreciated.
Thanks.
2.png
2.png
2.png
0
Comment
Question by:royjayd
  • 2
3 Comments
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
It looks like you are facing a bad database model design. How can you have a table with repeated values like that?

Instead of giving you a solution for a query, I think it's better to help you to get a normalized database model.
Can you post your table structure and the query that you are using?
Also if it's possible to know how you are insert data in that table (how and where's the data coming from)?
0
 

Author Comment

by:royjayd
Comment Utility
>>How can you have a table with repeated values like that?

We do have a normalized database model.
That was just a sample sql, i dint want to post the entire query since it contains INNER JOINS on more than 5 tables[The query itself is pretty big]
 I was focussing more on the result set and how to tweak the result set if possible.

Thanks.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
It will be very hard to work with a subset of the data and only with a small part of the query.
I will then suggest you to study the STUFF function together with FOR XML PATH option, since can be what you are looking for. Check the example #6 in this article.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now