?
Solved

Changing the layout of query results

Posted on 2007-07-20
2
Medium Priority
?
174 Views
Last Modified: 2010-05-18
How can I change the way data groups in a query
I just asked a question here to get the data I was looking for, but now I want it in a different layout.
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_22710379.html

instead of
Branch 1  Type: A    Percent: 20%
Branch 1  Type: B    Percent: 40%
Branch 1  Type: C    Percent: 20%
Branch 1  Type: D    Percent: 20%

Branch 2  Type: A    Percent: 40%
Branch 2  Type: B    Percent: 20%
Branch 2  Type: C    Percent: 20%
Branch 2  Type: D    Percent: 20%

Branch 3  Type: A    Percent: 40%
Branch 3  Type: B    Percent: 20%
Branch 3  Type: C    Percent: 20%
Branch 3  Type: D    Percent: 20%

I want it to display like this
Branch    Type A       Type B       TYPE C          Percent
1          count of A    count of B  count of C      Percentage of A for Branch 1
2          count of A    count of B  count of C      Percentage of A for Branch 2
3          count of A    count of B  count of C      Percentage of A for Branch 3

The query I am using right now is:

select branch, type, cast(count(*) as numeric(6,2))/cast((select count(*) as numeric(6,2)) from table where branch=t.branch)
from table t
group by branch, type

I am using sql2000
0
Comment
Question by:lexo
2 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 2000 total points
ID: 19536434
*if* you know exactly how many types there are, you can do this:

select branch,
(select count(*) from table where branch=t.branch and type='A') countA,
(select count(*) from table where branch=t.branch and type='B') countB,
(select count(*) from table where branch=t.branch and type='C') countC,
(select count(*) from table where branch=t.branch and type='D') countD,
(select count(*) from table where branch=t.branch and type='A') / (select count(*) from table where branch=t.branch) percA,
(select count(*) from table where branch=t.branch and type='B') / (select count(*) from table where branch=t.branch) percB,
(select count(*) from table where branch=t.branch and type='C') / (select count(*) from table where branch=t.branch) percC,
(select count(*) from table where branch=t.branch and type='D') / (select count(*) from table where branch=t.branch) percD

from table t
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19537314
Hello lexo,

Do you always have those four types, or can you have a varying number of types?  If always
those four, derekkromm should have you on the right track.  If not, and you cannot know
ahead of time what they are, we may need dynamic SQL...

Regards,

Patrick
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 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