Ugly SQL Pivot Reporting Queries Saved for Posterity

Here are some pivot reporting queries stored for future reference:

	SELECT
	CASE WHEN GROUPING(service) = 1 THEN 'All' ELSE service END service,
	SUM(CASE WHEN mm.service_grade_code = 'E01' THEN 1 ELSE 0 END) e01,
	100 * ROUND(CASE WHEN SUM(1) = 0 THEN 0 ELSE SUM(CASE WHEN mm.service_grade_code = 'E01' THEN 1 ELSE 0 END) / CAST(SUM(1) AS FLOAT) END, 4) e01_percentage,
	-- ... lots more service grade codes here
	SUM(CASE WHEN mm.service_grade_code = 'W05' THEN 1 ELSE 0 END) w05,
	100 * ROUND(CASE WHEN SUM(1) = 0 THEN 0 ELSE SUM(CASE WHEN mm.service_grade_code = 'W05' THEN 1 ELSE 0 END) / CAST(SUM(1) AS FLOAT) END, 4) w05_percentage,
	SUM(CASE WHEN mm.service_grade_code NOT IN ('E01','E02','E03','E04','E05','E06','E07','E08','E09','O01','O02','O03','O04','O05','O06','O07','O08','O09','W01','W02','W03','W04','W05') OR mm.service_grade_code IS NULL THEN 1 ELSE 0 END) unknown,
	100 * ROUND(
	CASE WHEN SUM(1) = 0 THEN
	0
	ELSE
	SUM(CASE WHEN mm.service_grade_code NOT IN ('E01','E02','E03','E04','E05','E06','E07','E08','E09','O01','O02','O03','O04','O05','O06','O07','O08','O09','W01','W02','W03','W04','W05') OR mm.service_grade_code IS NULL THEN 1 ELSE 0 END) / CAST(SUM(1) AS FLOAT)
	END, 4
	) unknown_percentage,
	SUM(1) total
	FROM
	evacuations e LEFT JOIN
	merged_movements mm ON e.id = mm.evacuation_id LEFT JOIN
	states s ON (s.state_machinable_id = e.id AND
	s.state_machinable_type = 'Evacuation') LEFT JOIN
	trauma_codes tc ON tc.id = e.trauma_code_id
	WHERE
	mm.origin_theater = 'CENTCOM' AND
	(mm.destination_theater = 'CONUS' OR mm.destination_theater = 'EUCOM' OR mm.destination_theater = 'PACOM') AND
	mm.operation in ('OEF','OIF') AND
	s.type = 'CompletedState' AND
	mm.active_duty = 1 AND
	is_injury = 0
	GROUP BY
	mm.service
	WITH ROLLUP
	ORDER BY
	mm.service

view raw gistfile1.sql hosted with ❤ by GitHub

	SELECT
	CASE WHEN GROUPING(
	CASE WHEN service_grade_code IN ('E01','E02','E03','E04','E05','E06','E07','E08','E09','O01','O02','O03','O04','O05','O06','O07','O08','O09','W01','W02','W03','W04','W05') THEN
	service_grade_code
	ELSE
	'Unknown'
	END
	) = 1 THEN
	'All Ranks'
	ELSE
	CASE WHEN service_grade_code IN ('E01','E02','E03','E04','E05','E06','E07','E08','E09','O01','O02','O03','O04','O05','O06','O07','O08','O09','W01','W02','W03','W04','W05') THEN
	service_grade_code
	ELSE
	'Unknown'
	END
	END rank,
	SUM(CASE WHEN mm.service = 'Air Force' THEN 1 ELSE 0 END) air_force,
	100 * ROUND(CASE WHEN SUM(1) = 0 THEN 0 ELSE SUM(CASE WHEN mm.service = 'Air Force' THEN 1 ELSE 0 END) / CAST(SUM(1) AS FLOAT) END, 4) air_force_percentage,
	-- ... other services Army, Navy
	SUM(CASE WHEN mm.service = 'Other' THEN 1 ELSE 0 END) other,
	100 * ROUND(CASE WHEN SUM(1) = 0 THEN 0 ELSE SUM(CASE WHEN mm.service = 'Other' THEN 1 ELSE 0 END) / CAST(SUM(1) AS FLOAT) END, 4) other_percentage,
	SUM(1) total
	FROM
	evacuations e LEFT JOIN
	merged_movements mm ON e.id = mm.evacuation_id LEFT JOIN
	states s ON (s.state_machinable_id = e.id AND
	s.state_machinable_type = 'Evacuation') LEFT JOIN
	trauma_codes tc ON tc.id = e.trauma_code_id
	WHERE
	mm.origin_theater = 'CENTCOM' AND
	(mm.destination_theater = 'CONUS' OR mm.destination_theater = 'EUCOM' OR mm.destination_theater = 'PACOM') AND
	mm.operation in ('OEF','OIF') AND
	s.type = 'CompletedState' AND
	mm.active_duty = 1 AND
	is_injury = 0
	GROUP BY
	CASE WHEN service_grade_code IN ('E01','E02','E03','E04','E05','E06','E07','E08','E09','O01','O02','O03','O04','O05','O06','O07','O08','O09','W01','W02','W03','W04','W05') THEN
	service_grade_code
	ELSE
	'Unknown'
	END
	WITH ROLLUP
	ORDER BY
	LEN(CASE WHEN service_grade_code IN ('E01','E02','E03','E04','E05','E06','E07','E08','E09','O01','O02','O03','O04','O05','O06','O07','O08','O09','W01','W02','W03','W04','W05') THEN
	service_grade_code
	ELSE
	'Unknown'
	END),
	CASE WHEN service_grade_code IN ('E01','E02','E03','E04','E05','E06','E07','E08','E09','O01','O02','O03','O04','O05','O06','O07','O08','O09','W01','W02','W03','W04','W05') THEN
	service_grade_code
	ELSE
	'Unknown'
	END

view raw gistfile1.sql hosted with ❤ by GitHub

For SQL help and insight I recommend this book: SQL Cookbook (Cookbooks (O'Reilly))