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
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
from table t
group by branch, type
I am using sql2000
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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