Link to home
Start Free TrialLog in
Avatar of lexo
lexo

asked on

Changing the layout of query results

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.
https://www.experts-exchange.com/questions/22710379/Query-grouping-and-percentage-by-group.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
ASKER CERTIFIED SOLUTION
Avatar of derekkromm
derekkromm
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Patrick Matthews
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