Percentage in Pivot table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Percentage in Pivot table

Author

Topic

webforyou
Starting Member

15 Posts

Posted - 2007-10-16 : 11:47:16

Dear all,
Can we do Percentage in Pivot table. I just see the sum, max case when we do the Pivot, but how about the percentage.
Ex:
Raw table
ID Policy Type Amt channel
1 134567 A 450000 1
2 234567 B 120000 2
3 123234 A 245000 3
4 454678 A 300000 4
5 145678 B 900000 1
6 345678 A 1000000 2
7 765433 B 2000000 2
8 121234 B 100000 3
9 222330 A 290000 3
10 111222 B 1000000 4
11 343456 A 2000000 1
12 565666 A 100000 2
13 126789 B 100000 2
14 765433 A 290000 3
15 121234 B 1000000 3
16 222330 B 300000 2
17 111222 A 5000000 3
18 442332 A 350000 3
19 555444 B 900000 4
20 234578 B 210000 4

The statistic, I do manually:

Type A Type B
Channel Amt % in channel % in type Amt % in channel % in type Total
1 2450000 24.44% 73.13% 900000 13.57% 26.87% 3350000
2 1100000 10.97% 30.39% 2520000 38.01% 69.61% 3620000
3 6175000 61.60% 84.88% 1100000 16.59% 15.12% 7275000
4 300000 2.99% 12.45% 2110000 31.83% 87.55% 2410000
Total 10025000 100.00% 6630000 100.00% 16655000

Hope to receive your help

Thanks all!

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2007-10-18 : 04:59:30

kindly repost your sample data and used code tags. It would be much readable for other members and you will get faster replies.

Want Philippines to become 1st World COuntry? Go for World War 3...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 05:14:23

[code]DECLARE @Raw TABLE (ID INT, Policy INT, [Type] CHAR(1), Amt INT, Channel INT)

INSERT @Raw
SELECT 1, 134567, 'A', 450000, 1 UNION ALL
SELECT 2, 234567, 'B', 120000, 2 UNION ALL
SELECT 3, 123234, 'A', 245000, 3 UNION ALL
SELECT 4, 454678, 'A', 300000, 4 UNION ALL
SELECT 5, 145678, 'B', 900000, 1 UNION ALL
SELECT 6, 345678, 'A', 1000000, 2 UNION ALL
SELECT 7, 765433, 'B', 2000000, 2 UNION ALL
SELECT 8, 121234, 'B', 100000, 3 UNION ALL
SELECT 9, 222330, 'A', 290000, 3 UNION ALL
SELECT 10, 111222, 'B', 1000000, 4 UNION ALL
SELECT 11, 343456, 'A', 2000000, 1 UNION ALL
SELECT 12, 565666, 'A', 100000, 2 UNION ALL
SELECT 13, 126789, 'B', 100000, 2 UNION ALL
SELECT 14, 765433, 'A', 290000, 3 UNION ALL
SELECT 15, 121234, 'B', 1000000, 3 UNION ALL
SELECT 16, 222330, 'B', 300000, 2 UNION ALL
SELECT 17, 111222, 'A', 5000000, 3 UNION ALL
SELECT 18, 442332, 'A', 350000, 3 UNION ALL
SELECT 19, 555444, 'B', 900000, 4 UNION ALL
SELECT 20, 234578, 'B', 210000, 4

SELECT COALESCE(CONVERT(VARCHAR(12), Channel), 'Total') AS Channel,
TypeA,
1.0 * TypeA / TypeTotal AS PercentageTypeA,
TypeB,
1.0 * TypeB / TypeTotal AS PercentageTypeB,
TypeTotal
FROM (
SELECT Channel,
SUM(CASE WHEN [Type] = 'A' THEN Amt ELSE 0 END) AS TypeA,
SUM(CASE WHEN [Type] = 'B' THEN Amt ELSE 0 END) AS TypeB,
SUM(Amt) AS TypeTotal
FROM @Raw
GROUP BY Channel
WITH ROLLUP
) AS p
ORDER BY CASE
WHEN Channel IS NULL THEN 1
ELSE 0
END,
Channel[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources