SQLTeam.com | Weblogs | Forums

Multiple pivot, can it be done?

sql-server-2008
tsql
sql2008

#1

I can generate the following table, can I do a 3 way pivot using stake, return and profit so it's grouped by season, rather than doing a union?

Season	Stake	Return	Profit
1	5.70	0.20	-5.5000
1	5.70	0.50	-5.2000
1	5.70	0.00	-5.7000
1	5.70	0.66	-5.0400
1	5.70	3.62	-2.0800
1	5.70	7.83	2.1300
1	5.70	0.00	-5.7000
1	5.70	3.64	-2.0600
1	5.70	0.70	-5.0000
1	5.70	0.00	-5.7000
1	5.70	60.86	55.1600
1	5.70	2.59	-3.1100
1	5.70	0.00	-5.7000
1	5.70	24.18	18.4800
1	5.70	49.49	43.7900
1	8.40	0.00	-8.4000
1	8.40	0.00	-8.4000
1	5.40	3.33	-8.0700
1	5.40	14.47	3.0700
1	8.40	0.00	-8.4000
1	8.40	11.68	3.2800
1	8.40	3.47	-4.9300
1	8.40	2.51	-5.8900
1	8.40	20.39	11.9900
1	8.40	94.36	85.9600
1	8.40	0.00	-8.4000
1	8.40	6.56	-1.8400
1	8.40	0.00	-8.4000
1	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	52.94	44.5400
2	8.20	34.61	26.0100
2	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	4.83	-3.5700
2	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	2.41	-5.9900
2	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	2.31	-6.0900
2	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	1.90	-6.5000
2	8.40	11.51	3.1100
2	8.40	9.26	0.8600
2	8.40	0.00	-8.4000
2	8.40	9.73	1.3300
2	8.40	0.00	-8.4000
2	8.40	2.35	-6.0500
2	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	0.00	-8.4000
2	8.40	2.06	-6.3400
2	8.40	0.00	-8.4000
2	8.40	16.90	8.5000
3	8.40	1.46	-6.9400
3	8.40	1.47	-6.9300
3	8.40	0.00	-8.4000
3	8.40	0.00	-8.4000
3	8.40	9.43	1.0300
3	8.40	0.00	-8.4000
3	8.40	0.00	-8.4000
3	8.40	0.00	-8.4000
3	8.40	0.00	-8.4000
3	8.40	8.47	0.0700
3	8.40	30.32	21.9200
3	8.40	6.56	-1.8400
3	16.80	12.96	-3.8400
3	16.80	2.35	-14.4500
3	16.80	12.65	-4.1500
3	16.80	12.84	-3.9600
3	16.80	2.17	-14.6300
3	16.80	9.11	-7.6900
3	16.80	43.94	27.1400
3	16.80	8.92	-7.8800
3	16.80	0.00	-16.8000
3	16.80	0.00	-16.8000
3	16.80	28.06	11.2600
3	16.80	8.28	-8.5200
3	16.80	0.00	-16.8000
3	16.80	9.52	-7.2800
3	16.80	25.26	8.4600
3	16.80	7.56	-9.2400
3	16.80	0.00	-16.8000
3	16.80	2.33	-14.4700
3	16.80	31.52	14.7200
3	16.80	12.69	-4.1100
3	16.80	2.49	-14.3100
3	16.80	4.04	-12.7600
3	16.80	10.27	-6.5300
3	16.80	24.86	8.0600
3	16.80	0.00	-16.8000
3	16.80	11.15	-5.6500
3	16.80	31.98	15.1800
4	16.80	0.00	-16.8000
4	16.80	0.00	-16.8000
4	16.80	25.56	8.7600
4	16.80	2.94	-13.8600
4	16.80	0.00	-16.8000
4	16.80	3.91	-12.8900
4	16.80	0.00	-16.8000
4	16.80	0.00	-16.8000
4	16.80	16.78	-0.0200
4	16.80	2.52	-14.2800
4	16.80	2.86	-13.9400
4	16.80	3.82	-12.9800
4	17.50	37.83	20.3300
4	17.50	16.40	-1.1000
4	17.50	17.50	0.0000
4	17.50	4.04	-13.4600
4	17.50	50.30	32.8000
4	17.50	15.83	-1.6700
4	17.50	6.25	-11.2500
4	17.50	22.44	4.9400
4	17.50	61.91	44.4100
4	17.50	13.26	-4.2400
4	17.50	14.23	-3.2700
4	17.50	11.67	-5.8300
4	17.50	14.34	-3.1600
4	17.50	5.13	-12.3700
4	17.50	16.10	-1.4000
4	17.50	5.13	-12.3700
4	17.50	16.46	-1.0400
4	17.50	14.17	-3.3300
4	17.50	4.25	-13.2500
4	17.50	3.50	-14.0000
4	17.50	4.17	-13.3300
4	17.50	4.88	-12.6200
4	17.50	13.65	-3.8500
4	17.50	5.75	-11.7500
4	17.50	37.17	19.6700
4	17.50	0.00	-17.5000
4	17.50	17.81	0.3100
4	17.50	5.13	-12.3700
4	17.50	52.32	34.8200
5	17.50	65.27	47.7700
5	17.50	16.06	-1.4400
5	17.50	4.58	-12.9200
5	17.50	13.08	-4.4200
5	17.50	0.00	-17.5000
5	17.50	5.00	-12.5000
5	17.50	0.00	-17.5000
5	17.50	14.69	-2.8100
5	17.50	4.00	-13.5000
5	17.50	13.21	-4.2900
5	17.50	58.01	40.5100
5	17.50	12.47	-5.0300
5	17.50	55.94	38.4400
5	17.50	18.13	0.6300
5	17.50	11.94	-5.5600
5	12.50	0.00	-22.5000
5	17.50	16.14	-1.3600
5	17.50	5.25	-12.2500
5	17.50	10.92	-6.5800
5	17.50	3.41	-14.0900
5	17.50	28.34	10.8400
5	17.50	55.03	37.5300
5	17.50	34.12	16.6200
5	17.50	10.36	-7.1400
5	17.50	12.50	-5.0000
5	17.50	0.00	-17.5000
5	17.50	12.63	-4.8700
5	17.50	37.22	19.7200
5	17.50	13.08	-4.4200
5	17.50	36.31	18.8100
5	17.50	34.33	16.8300
5	17.50	14.77	-2.7300
5	17.50	5.75	-11.7500
5	17.50	15.71	-1.7900
5	17.50	11.56	-5.9400
5	18.13	11.62	-6.5100
5	17.50	0.00	-17.5000
5	17.50	13.57	-3.9300
5	17.50	0.00	-17.5000
5	17.50	50.39	32.8900
5	17.50	13.24	-4.2600
5	17.50	9.55	-7.9500

#2

The data is already pivoted... You just need to apply the aggregation...

SELECT 
	td.Season,
	Stake = SUM(td.Season), 
	[Return] = SUM(td.[Return]), 
	Profit = SUM(td.Profit)
FROM
	#TestData td
GROUP BY 
	td.Season;